VLOOKUP AND FIND THE SECOND MATCH

[Deano]

Hi

Can I modify a VLOOKUP formula to not find the first match, but the second, third, etc.

 

[fairwinds]

Hi,

Formula needs to be entered with Ctrl + shift + enter.
Index range must always start at row 1 wherever your data is.

Book1
	A	B	C	D	E	F
1				Lookupval	Match
2	a	10		a	1	10
3	a	11			2	11
4	b	12			3	13
5	a	13
6	c	14
Sheet6

 

[Deano]

Thanks very much, works great on my test data.

I'm going to have to play with this one to fully understand how it works.

Cheers

 

[countryfan_nt]

HMMM thanks, is there an easier way to do it. Or atleast could you kindly explain the formula structure.

 

[fairwinds]

I think the formula is pretty straight forward. The IF statement gives you an array of the row numbers for the rows that have a match. SMALL gives you the the occurenc you specify. The result is used in INDEX to give you the value.

An easier way could be using a UDF called vlookupnth that you can find searching this board.

 

[countryfan_nt]

Hello, Quick question the formula works nicely, but why must it only work when the table is in row one (1)?

Thanks,
Nawaf

 

[Chitosunday]

Another option. Use user defined function
http://www.vbaexpress.com/kb/getarticle.php?kb_id=476

 

[fairwinds]

Hello, Quick question the formula works nicely, but why must it only work when the table is in row one (1)?

Thanks,
Nawaf

The table can be any where you must just start the range for INDEX in row 1

E.g. if table is A10:A20 you use A1:A20 as the range for INDEX.

 

[toddy98274]

Sorry to resurrect this thread but I am trying to use the UDF suggested and am having trouble

The problem with this is that my lookup isnt an exact match to the list it looks to

I have managed to doctor a vlookup function so that it looks for the item anywhere in the string using the following formula:

=VLOOKUP("*" & A1 & "*",'Sheet1'!D2:J241,7,FALSE)

However if i try and use the same sort of thing within the UDF or even the Index formula it returns no result. I am assuming that is because it doesnt recognise what I am looking for ie "*" & A1 & "*", but it is strange that the vlookup formula will recognise it

Any help appreciated

 

[Aladin Akyurek]

Hello, Quick question the formula works nicely, but why must it only work when the table is in row one (1)?

Thanks,
Nawaf

The table can be any where you must just start the range for INDEX in row 1

E.g. if table is A10:A20 you use A1:A20 as the range for INDEX.

You can avoid that 'prerequisite' easily though:

=INDEX($B$10:$B$20,SMALL(IF($A$10:$A$20=$D$2,ROW($A$10:$A$20)-ROW($A$10)+1),$E$2))

which needs to be confirmed with control+shift+enter.

Note that D2 houses a lookup value and E2 an instance number, e.g., 2.