Search for value in multiple columns

[cyoungtx]

Excel level - novice

I am using 2 sheets. I am using 2 columns on sheet 1. Column A contains ascending numerical values. Column B needs a formula.

I need to search for the values in column A sheet 1 on multiple columns on Sheet 2. The value will only appear once on each sheet. The formula should return a different result depending on what column it appears in on sheet 2, eg. Column A -"Red", Column B - "Blue", Column C - "Green". This value should return to sheet 1, column B.

Your help is greatly apprecieated!

 

[T. Valko]

Excel level - novice

I am using 2 sheets. I am using 2 columns on sheet 1. Column A contains ascending numerical values. Column B needs a formula.

I need to search for the values in column A sheet 1 on multiple columns on Sheet 2. The value will only appear once on each sheet. The formula should return a different result depending on what column it appears in on sheet 2, eg. Column A -"Red", Column B - "Blue", Column C - "Green". This value should return to sheet 1, column B.

Your help is greatly apprecieated!

Are those the column headers where the number will appear?

Like this:

Book1
	A	B	C
1	Red	Blue	Green
2	38	93	81
3	12	44	26
4	34	14	33
5	29	15	25
Sheet2

So, if we were searching for number 14 we'd want to return the column header Blue?

Is this correct?

 

[MrVillareal]

Hi

2 sheets
Sheet1 2 columns and 10 rows each
Sheet2 3 columns and 10 rows each

In sheet1 select all the rows in Column B
Go to Home Tab, Conditional Formatting, New Rule, Use formula, and Enter the following:

For Red:
=MAX(IF(Sheet2!$A$1:$C$10=Sheet1!A1,COLUMN(Sheet2!$A$1:$C$1)-COLUMN(Sheet2!$A$1)+1,""))=1

For Blue:
=MAX(IF(Sheet2!$A$1:$C$10=Sheet1!A1,COLUMN(Sheet2!$A$1:$C$1)-COLUMN(Sheet2!$A$1)+1,""))=2

For Green:
=MAX(IF(Sheet2!$A$1:$C$10=Sheet1!A1,COLUMN(Sheet2!$A$1:$C$1)-COLUMN(Sheet2!$A$1)+1,""))=3

Excel Workbook
	A	B
1	23	2
2	2	2
3	25	2
4	16	3
5	4	3
6	24	1
7	25	2
8	17	1
9	7	2
10	4	3
Sheet1

Excel 2010

#VALUE!

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.

 

[cyoungtx]

T. Valko, your response was accurate. I am trying to return the value of the color name. I can use it as the header for the column on sheet 2.

 

[cyoungtx]

MrVillareal, I am looking for column B sheet 1 to return the actual color name from the corresponding color column on sheet 2.

 

[T. Valko]

T. Valko, your response was accurate. I am trying to return the value of the color name. I can use it as the header for the column on sheet 2.

OK, based on the sample I posted...

On Sheet1 cell A2 = 14

Enter this array formula** in B2:

=INDEX(Sheet2!A1:C1,MAX(IF(Sheet2!A2:C5=A2,COLUMN(Sheet2!A2:C5)))-COLUMN(Sheet2!A2)+1)

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

 

[MrVillareal]

MrVillareal, I am looking for column B sheet 1 to return the actual color name from the corresponding color column on sheet 2.

Ok how about this.

Excel Workbook
	A	B
1	23	Blue
2	2	Blue
3	25	Blue
4	16	Green
5	4	Green
6	24	Red
7	25	Blue
8	17	Red
9	7	Blue
10	4	Green
Sheet1

Excel 2010

#VALUE!

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.

 

[MrVillareal]

Here is the sheet 2 for your verification.

Excel Workbook
	A	B	C
1	Red	Blue	Green
2	17	12	3
3	29	30	18
4	27	2	26
5	11	23	4
6	21	20	28
7	24	7	1
8	19	8	13
9	9	25	22
10	6	10	16
11	15	14	5
Sheet2

Excel 2010

 

[cyoungtx]

Thanks for the replies, I'm trying to get this to work properly. Can this work as an IF function, asking IF the number is added to sheet 1 and also appears in the columns of Sheet 2, then return the 'color' value. And if the value is not listed on Sheet 2 then the cell is left blank?

 

[T. Valko]

Thanks for the replies, I'm trying to get this to work properly. Can this work as an IF function, asking IF the number is added to sheet 1 and also appears in the columns of Sheet 2, then return the 'color' value. And if the value is not listed on Sheet 2 then the cell is left blank?

Yes, that can be done. The best approach depends on what version of Excel you're using?