Search for a value across multiple columns, and return the value in another column (same row)

[kdmorris85]

Hello Excel masters!

I know how to do a VLOOKUP or INDEX/MATCH to have it search a single column, but this is giving me trouble..I need to search multiple columns.

• I am given data in 1 column (A)
• I want to search for this data across multiple columns (H,I,J). In this example I put it on the same sheet, but this data is actually on Sheet2. I color coded the matching values so you can more easily see what needs to be done.
• I want it to return the corresponding value from column F
• There will not be any duplicates across the searched columns. Every value in H,I,J will be unique.

Please help, I would be eternally grateful.

 

[jolivanes]

Re: I want it to return the corresponding value from column F
Return to where?
Are the numbers always there or maybe some values not?

Sorry. Just noticed on your picture that you want column F equivalent in Column B beside the number you're looking for.
My apologies.

 

[jolivanes]

You could see if this does what you want on a copy of your workbook first.
Change the sheet references as required.

Sub Maybe()
    Dim c As Range
    For Each c In Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row)    '<----- No header. Change to A2 if you have a header
        c.Offset(, 1).Value = Sheets("Sheet3").Cells(Sheets("Sheet3").UsedRange.Find(c.Value, , , 1).Row, 6).Value
    Next c
End Sub

 

[kdmorris85]

You could see if this does what you want on a copy of your workbook first.
Change the sheet references as required.

Sub Maybe()
    Dim c As Range
    For Each c In Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row)    '<----- No header. Change to A2 if you have a header
        c.Offset(, 1).Value = Sheets("Sheet3").Cells(Sheets("Sheet3").UsedRange.Find(c.Value, , , 1).Row, 6).Value
    Next c
End Sub

Thank you for this, I will try it soon. To answer your question, yes, a value will always be in the column I want to pull (in this case F). There will never be a blank.

 

[jolivanes]

Thanks for keeping us up to date. It should work for you.
Good luck

 

[kdmorris85]

Thanks for keeping us up to date. It should work for you.
Good luck

I tried it with a full file and it worked great. I'd hate to take up any more of your time, but would it be possible to modify this code to pull D thru G rather than just F?

H,I,J are the columns to be searched. D,E,F,G has the data I want. Again, there will never be a duplicate of A anywhere in H,I,J. It will appear only once, or not at all.

Just F would be great but if it could pull D,E,F,G I can see this being very useful down the line.

Then have the results populate in B thru E. (I know in my example here it would overlap, but you get the idea since the other data is actually on another sheet.)

Thank you so very much, you are a god send. This would have taken me over a day.

 

[jolivanes]

Re: It will appear only once, or not at all.
I was under the impression that the value would always be in Sheet3.
I think that the "On Error Resume Next" is a viable option here.
If not, I hope someone will let us know.

Again, try this on a copy of your workbook first.

Sub Maybe()
    Dim c As Range, lr As Long
    lr = Sheets("Sheet3").UsedRange.Rows.Count
    For Each c In Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row)
        On Error Resume Next
        c.Offset(, 1).Resize(, 4).Value = Sheets("Sheet3").Cells(Sheets("Sheet3").Range("H1:J" & lr).Find(c.Value, , , 1).Row, 4).Resize(, 4).Value
        On Error GoTo 0
    Next c
End Sub

Please let us know if this is what you want.
Good luck

 

[jolivanes]

This is another option without the, sometimes disliked, On Error Resume Next.

Sub Maybe()
    Dim c As Range, lr As Long, tbc As Range
    lr = Sheets("Sheet3").UsedRange.Rows.Count
    For Each c In Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row)
       Set tbc = Sheets("Sheet3").Range("H1:J" & lr).Find(c.Value, , , 1)
        If Not tbc Is Nothing Then
        c.Offset(, 1).Resize(, 4).Value = Sheets("Sheet3").Cells(Sheets("Sheet3").Range("H1:J" & lr).Find(c.Value, , , 1).Row, 4).Resize(, 4).Value
        End If
    Next c
End Sub

If you're happy with one of these solutions, keep the other one in your back pocket in case you need it later on.

 

[Eric W]

There is a formula way to do this too. If your sheet 2 is set up as above, then you can do this:

	A	B	C	D	E
1
2	16-15	5	1/8	0.015	1.5
3	16-21	7	1/8	0.021	1.5
4	16-30	10	1/8	0.03	1.5
5	16-D30	10	1/8	0.03	1.5
6	16-33	11	1/8	0.033	1.5
7	wwww	No Match	No Match	No Match	No Match
8

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Array Formulas Cell Formula B2 {=IFERROR(INDEX(Sheet2!D$6:D$15,1/(1/MIN(IF(Sheet2!$H$6:$J$14=$A2,ROW($H$7:$J$15)-ROW($H$7)+1)))),"No Match")} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

Put the formula in B2, and confirm it with Control+Shift+Enter. Then you can copy that cell and paste it down and to the right.

 

[Jonmo1]

There will not be any duplicates across the searched columns. Every value in H,I,J will be unique.

If the given value from A will only occur once in columns H I or J, then try

=SUMPRODUCT(($H$6:$J$14=A1)*$F$6:$F$14)