#### TDK86

##### New Member

I've been mucking around for hours, but can't seem to get anything to work correctly.

What i need to do is match a single value, but this value could be in a number of different columns, please refer the below:

 Col A Col B Col C Col D Col E Col F Row 1 a 1 h 8 o 15 Row 2 b 2 i 9 p 16 Row 3 c 3 j 10 q 17 Row 4 d 4 k 11 r 18 Row 5 e 5 l 12 s 19 Row 6 f 6 m 13 t 20 Row 7 g 7 n 14 u 21

<tbody>
</tbody>

For example I need to lookup the associated value of "r" but need to search all columns, as this will be user input cell.

Does this make sense? can anyone help?

### Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
Hi,

I'm not sure there is a way to do this with a vlookup or simple formula. What output do you need from the lookup?

You could use something like the below as a custom formula to do the lookup across multiple cells. If you want it to find the text then output an adjacent column use the .offset at the end of the line.

Code:
Function customlookup(text As String)

Set customlookup = Range("A:F").Find(text, LookIn:=xlValues)

End Function

Here is a big nasty formula that should work with your example matrix:

<colgroup><col width="72"></colgroup>
Code:
<tbody>[TR]
[TD="width: 72"]=IF(NOT(ISNA(VLOOKUP("r",$A$1:$B$7,2,0))),VLOOKUP("r",$A$1:$B$7,2,0),IF(NOT(ISNA(VLOOKUP("r",$C$1:$D$7,2,0))),VLOOKUP("r",$C$1:$D$7,2,0),VLOOKUP("r",$E$1:$F$7,2,0)))
[/TD]
[/TR]
</tbody>

This array formula formula entered with Ctrl Shift Enter will do the trick. Adjust the range to suit your table

The cell values highlighted in red (H1) refers to your look up value ie "r"

=INDEX($A$1:$F$7,MIN(IF($A$1:$F$7=H1,ROW($A$1:$F$7)-MIN(ROW($A$1:$F$7))))+1,MIN(IF($A$1:$F$7=H1,COLUMN($A$1:$F$7)-MIN(COLUMN($A$1:$F$7))))+2)

Here is a big nasty formula that should work with your example matrix:

 =IF(NOT(ISNA(VLOOKUP("r",$A$1:$B$7,2,0))),VLOOKUP("r",$A$1:$B$7,2,0),IF(NOT(ISNA(VLOOKUP("r",$C$1:$D$7,2,0))),VLOOKUP("r",$C$1:$D$7,2,0),VLOOKUP("r",$E$1:$F$7,2,0)))

<tbody>
</tbody>
Thanks, works a treat....if i want to add additional column sets, what would i need to alter/add?

If a VLOOKUP() function does not find a match then it returns the #N/A error code. In the example above the ISNA() function is used to test if the VLOOKUP() functions found a match or not. So NOT(ISNA(VLOOKUP(…))) returns TRUE if a match is found and FALSE if not. Once you understand this it is just a matter of adding additional nested IF() tests to the formula.

But large formulas like this quickly become difficult to manage and understand. Depending on how many columns you are talking about, I would suggest spreading the formula out over multiple columns. For example doing a separate VLOOKUP() for each of the columns, and then testing for #N/A or a valid value in another column.

Depending on your requirements, you may also consider bringing the multiple columns into a single column on a separate worksheet with formulas and then just use a single VLOOKUP on that data.

Last edited:
Assuming the table starts in A1 with no headers and H1 contains the value you're looking up....

=SUMIF(A1:F7,H1,B1:F7)

A similar solution (with a similarly long formula) is

=IF(ISNUMBER(VLOOKUP(J1,A1:B7,2,FALSE)),VLOOKUP(J1,A1:B7,2,FALSE),0)+IF(ISNUMBER(VLOOKUP(J1,C1:D7,2,FALSE)),VLOOKUP(J1,C1:D7,2,FALSE),0)+IF(ISNUMBER(VLOOKUP(J1,E1:F7,2,FALSE)),VLOOKUP(J1,E1:F7,2,FALSE),0)

The test value is in J1, not "hard coded".

I posted it as I'd already typed it out but I like @Weazel's better.

Last edited:
By using multiple vlookups wrapped in an ISERROR/ISNA, this can cause your spreadsheet to become very show (depending on your data size)

Can I suggest using my formula in post #4 - You can just increase the range instead of having to stick in another VLOOKUP

Colin

Replies
9
Views
671
Replies
9
Views
243
Replies
6
Views
158
Replies
4
Views
309
Replies
2
Views
160

1,196,125
Messages
6,013,599
Members
441,777
Latest member

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back