VLookup from different tables using Aproximate match

[Avskedsfest]

I have a challenge

I would like to use Vlookup and aproximate match. But use different tables based in value in one cell. let me explain

If there is an "a" in first column I would like to run a Vlookup, searching for 10 in a table ranging from row z1:z10 (aproximate match). I there is a "b" in first column I would like to run a vlookup, searching for 120 in a table ranging from z11:z20 (aproximate match). etc

a 10
b 120
c 30

Any idea how I could make this happen?

 

[Domenic]

To return the approximate match from the relevant range, try...

=VLOOKUP(B2,CHOOSE(MATCH(A2,{"a","b","c"},0),$Z$1:$Z$10,$Z$11:$Z$20,$Z$21:$Z$30),1,TRUE)

...where A2 contains the letter, and B2 contains the lookup value.

Hope this helps!

 

[Avskedsfest]

Perfect, thanks

Two follow up questions.

It seems that I cannot refer the "a" "b" and "c" to cells? Instead of "a" I would like to write a cell such as G5. This would make life simpler as in reality I have long text string instead and not a

Question 2, I would like to refer to tables in another worksheet. But when I try this it does not work. Is this a limitation?

Many thanks for your help

 

[Avskedsfest]

hmmm, perhaps I ws too fast. It looks as if it works to move the table to a new sheet. I pressed F2 to review the formula and the colors of the reference were gone. I thought it was because it did not work, but it must have been due to the reference was in a different sheet....

 

[Domenic]

Try the following formula...

=VLOOKUP(B2,CHOOSE(MATCH(A2,$G$2:$G$4,0),'Sheet 2'!$Z$1:$Z$10,'Sheet 2'!$Z$11:$Z$20,'Sheet 2'!$Z$21:$Z$30),1,TRUE)

...where A2 contains the letter, B2 contains the lookup value, and G2:G4 contains "a", "b", and "c". Change the sheet name accordingly.

Hope this helps!

 

[Avskedsfest]

Perfect! Thanks a million