Vlookup 2nd value

[orzik]

All,
This is my challenge:
I need to lookup not only first value but als 2nd, 3rd etc.
I searched the board for any clues but couldn't find what I need.

In below example I would like to lookup phone numbers that belong to JOHN (E1). Since he is listed 3 times 3 numbers will be returned. I can spice it up just need the hard work idea to start.
Thanks,

Book1.xls
	A	B	C	D	E	F
1	Name	Phone		LookupName	John
2	John	222		1	222	vlookup1stvalue
3	Ana	333		2	212	vlookup2ndvalue
4	Tom	444		3	213	vlookup3rdvalue
5	John	212		4		vlookup4thvalue
6	John	213		5		vlookup5thvalue
7	Ana	313
8	Ana	555
Sheet1

 

[Aladin Akyurek]

Consider using AutoFilter.

 

[orzik]

Thanks for reply,
I used it but something else would be a better fit for me since I would like to combine it with Macros and I would like to display this info on a separate sheet as a form for selections for further processing.
Any ideas?

 

[Zack Barresse]

Try these UDF's originally developed by Peter Moran. They do exactly what you are asking for ...


<font face=Tahoma New>
<SPAN style="color:#00007F">Function</SPAN> VLOOKUPNTH(lookup_value, table_array <SPAN style="color:#00007F">As</SPAN> Range, _
           col_index_num <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>, nth_value)
<SPAN style="color:#007F00">' Extension to VLOOKUP function.  Allows for finding</SPAN>
<SPAN style="color:#007F00">' the    "   nth   "    item that matches the lookup value.</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> nRow <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> nVal <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> bFound <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN>
  VLOOKUPNTH = "Not Found"
  <SPAN style="color:#00007F">With</SPAN> table_array
    <SPAN style="color:#00007F">For</SPAN> nRow = 1 <SPAN style="color:#00007F">To</SPAN> .Rows.Count
      <SPAN style="color:#00007F">If</SPAN> .Cells(nRow, 1).Value = lookup_value <SPAN style="color:#00007F">Then</SPAN>
        nVal = nVal + 1
      <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
      <SPAN style="color:#00007F">If</SPAN> nVal = nth_value <SPAN style="color:#00007F">Then</SPAN>
        VLOOKUPNTH = .Cells(nRow, col_index_num).Text
        <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Function</SPAN>
      <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
    <SPAN style="color:#00007F">Next</SPAN> nRow
  <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN>

<SPAN style="color:#00007F">Function</SPAN> HLOOKUPNTH(lookup_value, table_array <SPAN style="color:#00007F">As</SPAN> Range, _
           Row_index_num <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, nth_value)
<SPAN style="color:#007F00">' Extension to HLOOKUP function.  Allows for finding</SPAN>
<SPAN style="color:#007F00">' the    "   nth   "    item that matches the lookup value.</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> nCol <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> nVal <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> bFound <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN>
  HLOOKUPNTH = "Not Found"
  <SPAN style="color:#00007F">With</SPAN> table_array
    <SPAN style="color:#00007F">For</SPAN> nCol = 1 <SPAN style="color:#00007F">To</SPAN> .Columns.Count
      <SPAN style="color:#00007F">If</SPAN> .Cells(1, nCol).Value = lookup_value <SPAN style="color:#00007F">Then</SPAN>
        nVal = nVal + 1
      <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
      <SPAN style="color:#00007F">If</SPAN> nVal = nth_value <SPAN style="color:#00007F">Then</SPAN>
        HLOOKUPNTH = .Cells(Row_index_num, nCol).Text
        <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Function</SPAN>
      <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
    <SPAN style="color:#00007F">Next</SPAN> nCol
  <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN></FONT>


HTH

 

[orzik]

FireFytr - thanks,
I do not seem to be able to modify it to work. Could you please help me out, maybe you could modify it so that it works on my example above and I could see your changes and modify it the way I need.

 

[Zack Barresse]

Sure. Like this ...

Book36
	A	B	C	D	E	F
1	Name	Phone		LookupName	John
2	John	222		1	222	vlookup1stvalue
3	Ana	333		2	212	vlookup2ndvalue
4	Tom	444		3	213	vlookup3rdvalue
5	John	212		4	NotFound	vlookup4thvalue
6	John	213		5	NotFound	vlookup5thvalue
7	Ana	313
8	Ana	555
Sheet1

Formula is ...

=VLOOKUPNTH($E$1,$A$2:$B$8,2,D2)


The difference being (from the native VLOOKUP) instead of specifying a match-type, you substitute the occurance number to retreive.

 

[orzik]

That's owsome!
Thanks FireFytr

 

[Zack Barresse]

You're welcome. Glad to help.


Take care!

 

[DanAtCorning]

Great Post - Dumb Question: What does the "nth value" variable represent? It seems like it's the cell location where we want to store the number representing which value number this is. But when I try to call the Function, and I enter "H2" after the column index number, (where you've entered D2), when I enter the closing parenthesis, I get "Compile error: Expected: =". I know I'm doing something dumb, just can't identify specifically WHAT. I also don't understand how the function knows to place the values that are paired with the lookup_value into column E (or in my case, column I).

 

[DanAtCorning]

Zack, great post - dumb question. When I try to use this UDF, I am running into a problem with the last variable you pass to it. When I call the function, and enter "H2" where you have "D2" as the nth_value variable, I get an error message that reads "Compile error: Expected: =". I don't understand what I'm doing wrong, but to be honest, I'm not sure I even understand exactly WHAT that variable's purpose is. Is it just pointing to the cell location where I want to enter WHICH nth value this is? Also, how does the function know to put the corresponding values in Column E? I know this is simple, but unfortunately, so am I