Nested Vlookup

**Althas** · 02-12-2009, 08:09 AM

Hi all

I have attached a small sample of some data I am working on (the total is about 6000 lines overall spread over 30 worksheets), but I am stuck trying to get a nested vlookup to work, can anyone help please?

What I have

A list of codes contained in 'A' and values in 'B'. I have grouped together the codes in colum 'A' starting with the same 4 digits, and gave them a named range. Columns G and H show all the possible range names. 'K' is a list of all the seperate codes (I know it is the same as 'A', but this is just an example to get a formula working)

What I would like formula column L

to lookup the first 4 digits in column 'K', use that value to lookup the range name in 'G & H', then using the FULL code in K, look for that in the corresponding name range and return the value from 'B'

I hope that makes sense, but I am sure looking at the formula I have tried will clarify it

Thanks

**DonkeyOte** · 02-12-2009, 08:44 AM

Not entirely sure I'm following the concept (presumably the embedded lookup will generate references to various sheets in your file) but for the purposes of your demo:

Please Login or Register  to view this content.

Note: INDIRECT is Volatile so if you have lots of them performance will be affected (adversely) -- see http://www.decisionmodels.com/calcsecretsi.htm for more info on Volatility and all things Calculation ...

**Althas** · 02-12-2009, 09:53 AM

Thanks Donk :-)

Yes, sorry I should have said, the range names will be on other sheets within the same workbook, does that make a dofference to this?

Cheers

**DonkeyOte** · 02-12-2009, 09:57 AM

If the references you're using in conjunction with INDIRECT are contained within the same workbook then that's fine... INDIRECT will run into issues when the target is external and closed at which point other approaches need to be sought (like morefunc.xll / INDIRECT.EXT). Doesn't sound like this is an issue for you though... beware of volatility though pending size of your file.