vlookup past first one

[dwbolland]

i need to do a vlookup of the second third values that are true

standard vlookups only look for and return on the first value that is true

please help

cheers
dan

 

[JWARD]

use index,match
example
=INDEX(C84:C90,MATCH(F86&"@"&G86,A84:A90&"@"&B84:B90,0))
Formula must be confirmed with Ctrl + shift + enter

 

[Oaktree]

Try searching the board for "vlookupnth"

One hit will be:
http://www.mrexcel.com/board2/viewtopic.php?t=91902&highlight=vlookupnth

 

[Aladin Akyurek]

There are some threads that discuss this type of queries. Also:

http://www.cpearson.com/excel/lookups.htm#ClosestMatch

 

[dwbolland]

nice one people! vlookupnth is a dream!

you are all heroes!

however it does take a long time for excel to calculate all the cells! any ideas on speeding up?

i have vlookupnth looking up two different coloumn returns from 1st to the tenth on upwards of 300 items!

can i make the cell not go into the vlookupnth using if() or not? and will this even speed it up?

i am currently trying:
=IF(K21="Not Found",,VLOOKUPNTH($B21,'[All DN C4.xls]18-24'!$B:$F,4,3))

will this speed it up as the vast majority will return not found normally