Multiple VLOOKUP threads

[TDK86]

Please Help!!

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?

 

[Bairn90]

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.

Function customlookup(text As String)


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


End Function

 

[Hamburgler]

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

<colgroup><col width="72"></colgroup>

<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>

 

[TheCman81]

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)

 

[TDK86]

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?

 

[Hamburgler]

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.

 

[Weazel]

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

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

 

[Teeroy]

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.

 

[TheCman81]

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