Formula to lookup cells between ranges of columns and rows?

[Lou1]

This thread might be more appropriate titled: "What would be the formula to find if any cells in a column's range matches any cells in a row's range of cells.....
and then return a yes or no value for that particular row.'

My guess is that this can be done with a vlookup, but maybe not. So far I have been unable to do it but I'm sure it can be done.

EXAMPLE
*I have a column of cells:
Blue
White
Red
Magenta
etc

*and I want to see if any of the cells in that column match a particular row of data (the commas in the example represent separate cells in the example row)
Row 1: Green, Yellow, Red, Purple
Row 2: Blue, Magenta, White, Black

In this example, Row 1 would show a 'yes' because the color 'Red' matches. Row 2 would show a 'yes' because the color 'Magenta' matches.

***It's necessary that the 'yes' or 'no' appears on the same row in which a match has been found. (in order to keep the data organized).

Thanks for your help with this question!

 

[T. Valko]

This thread might be more appropriate titled: "What would be the formula to find if any cells in a column's range matches any cells in a row's range of cells.....
and then return a yes or no value for that particular row.'

My guess is that this can be done with a vlookup, but maybe not. So far I have been unable to do it but I'm sure it can be done.

EXAMPLE
*I have a column of cells:
Blue
White
Red
Magenta
etc

*and I want to see if any of the cells in that column match a particular row of data (the commas in the example represent separate cells in the example row)
Row 1: Green, Yellow, Red, Purple
Row 2: Blue, Magenta, White, Black

In this example, Row 1 would show a 'yes' because the color 'Red' matches. Row 2 would show a 'yes' because the color 'Magenta' matches.

***It's necessary that the 'yes' or 'no' appears on the same row in which a match has been found. (in order to keep the data organized).

Thanks for your help with this question!

Try this...

Book1
	A	B	C	D	E	F	G
2	Blue		Green	Yellow	Red	Purple	Yes
3	White		Blue	Magenta	White	Black	Yes
4	Red
5	Magenta
Sheet2

Formula entered in G2 and copied down:

=IF(SUMPRODUCT(--(ISNUMBER(MATCH(C2:F2,A$2:A$5,0)))),"Yes","No")

 

[Lou1]

Thanks for your rapid response T. Valko.

I need to do a related task that you or another member of the community could help with:

As a follow-up to the example in this thread:

I need to be able to show the actual cell value that matches rather than just a 'yes' or 'no.' To put it another way, I need to display which cells in a column's range matches the cells in a row's range of cells.

SAME EXAMPLE WITH A SLIGHT DIFFERENCE
I want to see if any of the cells in a particular column match a particular row of data.

Below is a visual example.

In this example, Row 1 displays 'Red' because the color 'Red' matches. Row 2 displays 'Magenta' because the color 'Magenta' matches.

***NOTE: I need to make sure that the matching value is displayed on the same row in which it was found. So for example, Row 1 would display the color 'Red' on the same row (in Row 1).

Lou

 

[Lou1]

Looks like the visual example did not appear in my latest reply: the link for the image is available at:
https://picasaweb.google.com/lh/photo/EDAmYympZTtxQ4mQxQO0yn36CG2i1THEiLIU_iTIRDI?feat=directlink

<table style="width:auto;"><tr><td><a href="https://picasaweb.google.com/lh/photo/EDAmYympZTtxQ4mQxQO0yn36CG2i1THEiLIU_iTIRDI?feat=embedwebsite"><img src="https://lh6.googleusercontent.com/_S3A0fYh1X1o/TcR1sfwAd4I/AAAAAAAAAhA/gtlVnE5eISE/s144/excel2.jpg" height="53" width="144" /></a></td></tr><tr><td style="font-family:arial,sans-serif; font-size:11px; text-align:right">From <a href="https://picasaweb.google.com/jeremycotter/LightCompassBoostTrafficAndROI?authkey=Gv1sRgCNrhrNTqw52RhAE&feat=embedwebsite">LightCompass - Boost Traffic and ROI</a></td></tr></table>

 

[T. Valko]

Looks like the visual example did not appear in my latest reply: the link for the image is available at:
https://picasaweb.google.com/lh/photo/EDAmYympZTtxQ4mQxQO0yn36CG2i1THEiLIU_iTIRDI?feat=directlink

<TABLE style="WIDTH: auto"><TBODY><TR><TD></TD></TR><TR><TD style="FONT-SIZE: 11px; FONT-FAMILY: arial,sans-serif; TEXT-ALIGN: right">From LightCompass - Boost Traffic and ROI</TD></TR></TBODY></TABLE>

Ok, what if there is more than one matching entry? Also, what result do you want if there are no matching entries?

 

[Marcelo Branco]

Hi,

Try this array-formula

=INDEX(C1:F1,MATCH(1,1-ISNA(MATCH(C1:F1,$A$1:$A$4,0))))
Confirmed with Ctrl+Shift+Enter
(hold down both Ctrl and Shift keys and hit Enter)

HTH

M.
ps: if more than one color in a row match column A the formula shows only the right-most

 

[Marcelo Branco]

-EDIT-

Better

Excel 2007 or higher
=IFERROR(INDEX(C1:F1,MATCH(1,1-ISNA(MATCH(C1:F1,$A$1:$A$4,0)))),"No match")
Ctrl+Shift+Enter

-EDIT-
oops..
Disregard this post


M.

 

[Marcelo Branco]

This is Ok
=IFERROR(INDEX(C1:F1,MATCH(1,1-ISNA(MATCH(C1:F1,$A$1:$A$4,0)),0)),"No match")
Ctrl+Shift+Enter

Gets the left-most if more than one match

M.

 

[Lou1]

This worked perfectly Marcelo - thank you! And I also appreciate you reminding to do the Ctrl+Shift+Enter (rather than just Enter) to ensure the formula worked.

 

[Marcelo Branco]

This worked perfectly Marcelo - thank you! And I also appreciate you reminding to do the Ctrl+Shift+Enter (rather than just Enter) to ensure the formula worked.

You are welcome.

Tks for providing feedback

M.