From Vlookup to Index

**arazoe** · 09-24-2010, 02:01 PM

Hi,

I'd like to reduce the usage of Vlookups, any idea how I can shorten the formula below and use another function, for example, Index - or, any other function that will do the trick?

=IF('Analysis View'!$A$55="QTR 1 Actual",(VLOOKUP($A132,Mapping!$C$13:$Y$488,Mapping!$I$10,FALSE)),IF('Analysis View'!$A$55="QTR 2 Actual",(VLOOKUP($A132,Mapping!$C$13:$Y$488,Mapping!$N$10,FALSE)),IF('Analysis View'!$A$55="QTR 3 Actual",(VLOOKUP($A132,Mapping!$C$13:$Y$488,Mapping!$O$10,FALSE)),IF('Analysis View'!$A$55="QTR 4 Actual",(VLOOKUP($A132,Mapping!$C$13:$Y$488,Mapping!$P$10,FALSE))))))

Thanks!

**NBVC** · 09-24-2010, 02:11 PM

Try:

=VLOOKUP($A132,Mapping!$C$13:$Y$488,CHOOSE(MID('Analysis View'!$A$55,5,1)+0,Mapping!$I$10,Mapping!$N$10,Mapping!$O$10,Mapping!$P$10),FALSE)

**arazoe** · 09-27-2010, 09:18 AM

Hi,

Thanks for your response. I'm not familiar with the formula you entered as CHOOSE(MID('Analysis View'!$A$55,5,1)+0,. The formula works and if you don't mind, will please expain what it is doing?

Thank you!

**arazoe** · 09-27-2010, 09:32 AM

Hi,

The original nested Vlookup formulas are looking at a QTR (1-4) filter and then extracting from a table / database the value that corresponds to the selected QTR.

thanks!

**NBVC** · 09-27-2010, 02:17 PM

Originally Posted by arazoe

Hi,

Thanks for your response. I'm not familiar with the formula you entered as CHOOSE(MID('Analysis View'!$A$55,5,1)+0,. The formula works and if you don't mind, will please expain what it is doing?

Thank you!

Choose() syntax:

CHOOSE(Index_num,Value1,Value2,Value3, etc)

Index_num is a number from 1 to max number of values you enter.

You can use a formula to get the Index_num, in this case I used Mid() formula.. which extracted the number out of the entry in 'Analysis View'!$A$55. The corresponding item in the values list would be extracted and evaluated, and then supplied to the Vlookup function.