How about bringing back the 2nd result from a hlookup? 1

[Wray69]

OK, how about this.... I have a table where I am bring back the 1,2,3, result, and then doing a hlookup. The problem I have is if say the 1st and 2nd result match I only get the 1st result from my hlookup. Is there anyway around this? So I can get the two different results if they match? Hope that made sense.

Regards,

Wray

 

[ETID]

Do you mean a matrix lookup...both cols and rows?

 

[Zathras]

Assuming you can use VBA, Use HLOOKUP to get the first one, then use this user-defined function to get the next (and the next, etc.):
[blue]

Function HLOOKUPNEXT(lookup_value, table_array As Range, _
           row_index_num As Long, last_value)
' Extension to HLOOKUP function.  Allows for finding
' the "next" item that matches the lookup value.
Dim nCol As Integer
Dim bFound As Boolean
  HLOOKUPNEXT = ""
  With table_array
    For nCol = 1 To .Columns.Count
      If .Cells(1, nCol).Text = lookup_value Then
        If bFound = True Then
          HLOOKUPNEXT = .Cells(row_index_num, nCol).Text
          Exit Function
        Else
          If .Cells(row_index_num, nCol).Text = last_value Then
            bFound = True
          End If
        End If
      End If
    Next nCol
  End With
End Function

[/color]

 

[Wray69]

Hey Zathras,

Thanks for the code. Im not the best at VBA though... I should hit ALT + F11 to get to the Editor and then paste this in, correct? If so Im doing something wrong cause I get #NAME. Any ideas where I am messing up? Also would love to learn more VBA, I can write normal EXCEL functions all day. Have any suggested sites that I could teach myself VBA from?

Regards,

Wray

 

[Zathras]

Right. Alt-F11 to get to the Editor, Select Insert/Module from the menu and paste in the code. At that point, you can close the VBA editor or not, it doesn't matter.

Go back to the spreadsheet and you should be able to use HLOOKUPNEXT just as if it came from Redmond.

Here is the sample data setup I used:
[blue]

D15: 'Smith
E15: 'Smith
F15: 'Jones
G15: 'Jones
H15: 'Jones
I15: 'Archer
J15: 'Archer
K15: 'Archer
L15: 'Archer
M15: 'Archer
D15: 'Apples
E15: 'Pears
F15: 'Bananas
G15: 'Peaches
H15: 'Plums
I15: 'Grapes
J15: 'Kumquats
K15: 'Passion Fruit
  (etc.)
A18: 'Smith
A19: 'Jones
A20: 'Archer
B18: =HLOOKUP($A18,$D$15:$M$16,2,0)
  (copy to B19 and B20)
C18: =HLOOKUPNEXT($A18,$D$15:$M$16,2,B18)
  (copy to C18 thru D20)

[/color]
You should then see (in A1820):

Smith    Apples    Pears
Jones    Bananas   Peaches    Plums
Archer   Grapes    Kumquats   Passion Fruit

I also have a VLOOKUPNEXT in case you want to re-arrange your data to a vertical format. See thread68-547478.

Here is a thread on VBA with links to other threads you might find useful: thread707-541579

 

[Wray69]

Thanks Zathras,

I still must be doing something incorrect, I even used your example and still get #NAME. I will try it again when I get to work tomorrow. Might work better after some sleep...heh

And thanks for the links.

Cheers Wray

 

[Wray69]

Well I did get it to work with your example and I stopped getting #NAME in my workbook that I am applying it to. But now it returns a blank result in my workbook. Any ideas?

Regards,

Wray

 

[Zathras]

First guess would be that the data is not as clean as you suppose: You may have trailing blanks, leading blanks, misspelled words, etc. HLOOKUP (and HLOOKUPNEXT) can only work with the data presented to it.

It has been tested and works with Excel97 on Win98. What is your setup? (XP seems to have some compatibility issues.)

Geoff, if you're listening can you do a quick test to see whether I'm missing something, or assuming something that needs to be set up. (Tools/References... perhaps?)

 

[Wray69]

I have tried it on Office 2k here at work and XP at home. I get a result from the hlookup, but the custom function hlookupnext comes up with a blank result. But it does work when I use your example data... I think when I get home tonight I will upload it to the web if you wanna take a look at it. I appreciate all your help so far.

Regards,

Wray

 

[Zathras]

...I get a result from the hlookup...

If you change the search value in the lookup range where the hlookup is getting a result, does the hlookup then get the next result?

I.e., change this:

Jones    Jones    Smith    Smith
Tax      Spend    Tax      Spend

where you presumably are retrieving the value "Tax" for "Smith", into this:

Jones    Jones    Green    Smith
Tax      Spend    Tax      Spend

Do you now see "Spend" as a result of the HLOOKUP when searching with "Smith"?

If not, then HLOOKUPNEXT wouldn't either.

 

[Wray69]

OK, I am still getting a blank result. I uploaded the file to here

http://home.attbi.com/~wray69/SAMPLE.xls

If you have time and dont mind please take a look. If not thats cool to, Im sure I will eventually figure out where I am going wrong.

Cheers,

Wray

 

[Zathras]

Ok, there are a couple of problems. First, a correction to my code:
[blue][/code]
Function HLOOKUPNEXT(lookup_value, table_array As Range, _
row_index_num As Long, last_value)[/code][green]

' Extension to HLOOKUP function.  Allows for finding
' the "next" item that matches the lookup value.

[/color]

Dim nCol As Integer
Dim bFound As Boolean
  HLOOKUPNEXT = ""
  With table_array
    For nCol = 1 To .Columns.Count
      If .Cells(1, nCol).Value = lookup_value Then
        If bFound = True Then
          HLOOKUPNEXT = .Cells(row_index_num, nCol).Text
          Exit Function
        Else
          If .Cells(row_index_num, nCol).Value = last_value Then
            bFound = True
          End If
        End If
      End If
    Next nCol
  End With
End Function

[/color]
Then here are the formulas you need:
[blue]

G4: =IF(F4=H4,HLOOKUPNEXT(H4,$K4:$W$205,$A4,E4),HLOOKUP(H4,$K4:$W$205,$A4,FALSE))
I4: =IF(H4=J4,HLOOKUPNEXT(J4,$K4:$W$205,$A4,G4),HLOOKUP(J4,$K4:$W$205,$A4,FALSE))

[/color]
Copy down to row 204.

You see, you need to use HLOOKUP when the 2nd value is different from the 1st, but use HLOOKUPNEXT when the 2nd value is the same as the first.

 

[Wray69]

Thanks Zathras, I really appreciate all of your help.

Cheers,

Wray