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.

 

[Aladin Akyurek]

[...]

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")} <tbody> </tbody>

<tbody>
</tbody>

[...]

Let's not invoke the expression 1/(1/X) because it's risky. (1/X) might turn up to be a decimal of 16 digits. Excel allows for 15 and rounds the 16th digit, making the expression to return a wrong number. (I encountered this problem recently and made a note this behavior, but I cannot find the thread right now.)

 

[Eric W]

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)

Very nice. If the only values to be returned are numeric, then this works great. In fact, you can easily tweak it to handle multiple columns:

B2: =SUMPRODUCT((Sheet2!$H$6:$J$14=$A2)*Sheet2!D$6:D$14)

copy across and down. A not-found condition will return a 0 value.

Let's not invoke the expression 1/(1/X) because it's risky. (1/X) might turn up to be a decimal of 16 digits. Excel allows for 15 and rounds the 16th digit, making the expression to return a wrong number. (I encountered this problem recently and made a note this behavior, but I cannot find the thread right now.)

You raise an interesting point. I'd seen the 1/(1/x construction and wondered if there might be rounding issues. I haven't seen any personally yet, so I continued to use it. If you can find the thread, I'd be interested. In this case, I'm performing the 1/1/x on the row number, with a value of 0 meaning no match. By using the array portion twice (inefficient), I could avoid the 1/1/x. Using the 1/1/x, if the row is 7 (for example), then we'd get a unending decimal with potential rounding issues. I wondered if putting a ROUND() around it, if that might alleviate some potential problems. As it turns out though, INDEX seems to have some rounding built into it. INDEX(A1:A6,2.999999) will return the 2nd item in the list, while INDEX(A1:A6,2.9999999) returns the 3rd item. With just over a million possible rows (7 digits), 15 digits of precision would seem to be enough to handle row numbers and still stay within INDEX's rounding ability. I know I'm making a lot of assumptions here, so it's very possible I'm missing something. More information / opinions welcome.

 

[kdmorris85]

Thank you all for your contributions. They all worked to do what I needed, I appreciate the overwhelming response. I'm sure someone else out there will also find this useful. If I can recall, this isn't the first time I've had to do this, but before it was just a small amount of data so I was able to do it manually. Thank you all again, I wish I was half as competent as you guys.

 

[jolivanes]

Just noticed now!!!!
In Post #8, the following line

c.Offset(, 1).Resize(, 4).Value = Sheets("Sheet3").Cells(Sheets("Sheet3").Range("H1:J" & lr).Find(c.Value, , , 1).Row, 4).Resize(, 4).Value

should be

c.Offset(, 1).Resize(, 4).Value = Sheets("Sheet3").Cells(tbc.Row, 4).Resize(, 4).Value