Need help pulling values from column - hlookup?

[ww2excel]

Hi all, so here's my issue.. I need to pull certain values from a single column. The column is formatted so that Product ID's are typically on the top row and the UPC is located on the row directly below (not all Product ID's have UPC codes below them but I need to grab all the ones that do). This column continues on this way for thousands of rows. I need to use my list of Product ID's in Column A to run a lookup and pull the UPC value on the row directly below the ID inside Column B (some will return the next Product ID if a UPC does not exist but I can filter these out later).

Here is an example of my sheet:

Column A:
IDNUM111
IDNUM222
IDNUM333
IDNUM444
IDNUM555

Column B:
IDNUM111
000111
IDNUM222
IDNUM333
IDNUM444
000444

Column A and B are in no particular order. I need some way to run a lookup based on the ID values in Column A and return the row directly below this value in Column B. I'm currently using the hlookup formula =HLOOKUP(A2, $B$2:$B$37960,2,FALSE) but this only returns the first UPC value and then stops. I need this to continue searching down all of my lookup value rows.

Any help would be much appreciated!

 

[Aladin Akyurek]

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][/tr]
[tr][td]

1​

[/td][td] [/td][td][/td][td][/td][td]

2​

[/td][td][/td][/tr]


[tr][td]

2​

[/td][td] IDNUM111[/td][td] IDNUM111[/td][td][/td][td]IDNUM111[/td][td]000111[/td][/tr]


[tr][td]

3​

[/td][td] IDNUM222[/td][td] 000111[/td][td][/td][td]IDNUM444[/td][td]000444[/td][/tr]


[tr][td]

4​

[/td][td] IDNUM333[/td][td] IDNUM222[/td][td][/td][td][/td][td][/td][/tr]


[tr][td]

5​

[/td][td] IDNUM444[/td][td] IDNUM333[/td][td][/td][td][/td][td][/td][/tr]


[tr][td]

6​

[/td][td] IDNUM555[/td][td] IDNUM444[/td][td][/td][td][/td][td][/td][/tr]


[tr][td]

7​

[/td][td] [/td][td] 000444[/td][td][/td][td][/td][td][/td][/tr]
[/table]

In D1 enter:

=SUM(IF(1-(LEFT($B$2:$B$7,2)="ID"),1))<strike></strike>

In D2 enter and copy down:

=IF($E2="","",INDEX($B$2:$B$7,MATCH($E2,$B$2:$B$7,0)-1))<strike></strike>

In E2 control+shift+enter, not just enter, and copy down:

=IF(ROWS($E$2:E2)<=$D$1,INDEX($B$2:$B$7,SMALL(IF(1-(LEFT($B$2:$B$7,2)="ID"),
    ROW($B$2:$B$7)-ROW($B$2)+1),ROWS($E$2:E2))),"")

Hope this helps.
<strike></strike>

 

[ww2excel]

Thanks for the quick response. I should clarify that not all Product ID's begin with "ID". These are a random assortment of letters and numbers that also vary in character lengths. Product UPC numbers also don't always begin with "000" and range in length as well.

 

[Kuljack]

How about a macro instead?

Sub RunThrough()

Dim Key As String
Dim ID As String
Dim UPC As String
Dim Pull As Range
Dim Drop As Range

Dim Check As String

For Each x In Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp))
    Key = Cells(x.Row, 1)
    For Each y In Range(Cells(1, 2), Cells(Rows.Count, 2).End(xlUp))
        ID = Cells(y.Row, 2)
        If Key = ID Then
            Set Pull = Cells(y.Row, 2)
                UPC = Pull.Offset(1, 0)
                Check = Left(UPC, 5)
                    If Check <> "IDNUM" Then
                        Set Drop = Cells(x.Row, 3)
                            Drop.Value = UPC
                    End If
                GoTo 1
        End If
    Next
1
Next

End Sub

 

[Kuljack]

Thanks for the quick response. I should clarify that not all Product ID's begin with "ID". These are a random assortment of letters and numbers that also vary in character lengths. Product UPC numbers also don't always begin with "000" and range in length as well.

You can expand the macro out to address additional variances within the data:

Sub RunThrough()

Dim Key As String
Dim ID As String
Dim UPC As String
Dim Pull As Range
Dim Drop As Range

Dim Check As String

For Each X In Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp))
    Key = Cells(X.Row, 1)
    For Each y In Range(Cells(1, 2), Cells(Rows.Count, 2).End(xlUp))
        ID = Cells(y.Row, 2)
        If Key = ID Then
            Set Pull = Cells(y.Row, 2)
                UPC = Pull.Offset(1, 0)
                Check = Left(UPC, 5)
                    If Check <> "IDNUM" Then
[COLOR="#FF0000"]                        If Check <> "This" Then
                            If Check <> "That" Then
                                If Check <> "Whatever" Then[/COLOR]
                                    Set Drop = Cells(X.Row, 3)
                                        Drop.Value = UPC
[COLOR="#FF0000"]                                End If
                            End If
                        End If[/COLOR]
                    End If
                GoTo 1
        End If
    Next
1
Next

End Sub

 

[Aladin Akyurek]

Thanks for the quick response. I should clarify that not all Product ID's begin with "ID". These are a random assortment of letters and numbers that also vary in character lengths. Product UPC numbers also don't always begin with "000" and range in length as well.

A macro would be a good idea here indeed...