-3

enter image description here

Sorry, I can't post image here because of restriction. But, I can't find another way to explain my problem.

I have a sheet like that. And then, I need cell B5 to return a value based on data from table below ID respectively. In cell A1, I used drop down list that contains IDs.

For example:

When A1 is 1 then B5 will return value from L5 and C5 will return value from M5.

when A1 is 2 then B5 will return value from L5 and C5 will return value from M5.

So, I create a formula in B5 like this:

=LOOKUP(A5,LOOKUP($A$1,$E$5:$E$14,$G$5:$G$14),LOOKUP($A$1,$E$5:$E$14,$H$5:$H$14))

And, then I stuck to make G5, H5 and I5 to return a range.

Any solutions?

EDIT: Sorry, I made a mistake. I've edited the example formula above.

UPDATE DETAILS:

Assuming A1 is 1. Then the expected formula will be like this:

=LOOKUP(A5, value of G5 , value of H5)

which G5 for ID 1 should have range K5:K14 and H5 for ID 1 should have range L5:L14 (here's my problem).

if only I can type K5:K14 in G5 and L5:l14 in H5, and both can work as ranges, I think my problem should be solved.

Improve this question
2
  • 3
    I'm confused. Your examples for A1 say the same values should be returned in either case.
    – Iszi
    Commented Oct 21, 2014 at 15:58
  • 2
    And you post, as part of your question, a formula where the parentheses aren't even balanced! And you say you want B5 pulling a value from Column L, but then you show a formula where B5 looks at Columns H and I!
    – Scott - Слава Україні
    Commented Oct 21, 2014 at 16:29

1 Answer 1

Reset to default
0

I have been looking at your problem and I think I know what you may be looking for. If you were to type in the ranges in G5:G14, H5:H14 & I5:I14 then you could use the INDIRECT function to reference them as part of your VLOOKUP formula. However, I do not believe that is necessary and INDIRECT should be avoided if possible due it being a volatile function (among other reasons).

    enter image description here

The formula in B5 is =IF(ISNUMBER($A$1),VLOOKUP($A5,$H$5:$AT$14,MATCH($A$1,$I$1:$AU$1,0)+B$4,FALSE),""). This uses MATCH to find which group of name data to bring into B5:B14 from the VLOOKUP on column A. Note that I've use 1 and 2 in B4:C4 with a custom number format of V\alu\e0 to help in the formula.

I've made that sample worksheet available on my OneDrive here for you to reference and download. Post back with any questions that you may still have and I will try to assist.

    Name1-10.xlsx

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .