Indirect and lookup to left

[Kit]

I have a wookbook with several sheets - based on a choice that is in a
dropdown list in L2 would determine which sheet the lookup is done on.
I have it working on one sheet that is a normal vlookup - with the
lookup value on the left and the required field to the right.

=IF(ISNA(VLOOKUP(B2,INDIRECT("'"&$L$2&"'!$a$1:$f$499"),2,FALSE)),"",VLOOKUP(B2,INDIRECT("'"&$L$2&"'!$a$1:$f$499"),2,FALSE))

Basically looking up the TC and returning the Description

TC Description Value...
111 accts payable Mary
112 accts rec Tom


Using the same sheets I have another spreadsheet that I need to be
able to lookup the description and return the TC.

I cannot for the life of me figure it out - it's been a long day

Please help

 

[Aladin Akyurek]

Try something like...

=INDEX(TCrange,MATCH(Description,DescriptionRange,0)

where you plug in the required INDIRECT bits.

BTW, you're using fairly expensive formulas: The volatile INDIRECT coupled
with "computing the same thing twice".

 

[Frank Kabel]

Hi
at first glance this looks o.k. What error do you get?

 

[Kit]

Thank you - this worked perfectly

Try something like...

=INDEX(TCrange,MATCH(Description,DescriptionRange,0)

where you plug in the required INDIRECT bits.

BTW, you're using fairly expensive formulas: The volatile INDIRECT coupled
with "computing the same thing twice".