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!

 

[trungnguyen]

AB1 thang 8

<!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:PunctuationKerning/> <w:ValidateAgainstSchemas/> <w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid> <w:IgnoreMixedContent>false</w:IgnoreMixedContent> <w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText> <w:Compatibility> <w:BreakWrappedTables/> <w:SnapToGridInCell/> <w:WrapTextWithPunct/> <w:UseAsianBreakRules/> <w:DontGrowAutofit/> </w:Compatibility> <w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel> </w:WordDocument> </xml><![endif]--><!--[if gte mso 9]><xml> <w:LatentStyles DefLockedState="false" LatentStyleCount="156"> </w:LatentStyles> </xml><![endif]--><!--[if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin:0in; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman"; mso-ansi-language:#0400; mso-fareast-language:#0400; mso-bidi-language:#0400;} </style> <![endif]--> [FONT=&quot]不動産　徳島[/FONT]
[FONT=&quot]徳島　賃貸[/FONT]
[FONT=&quot]不動産　徳島　売買[/FONT]

 

[cyoungtx]

I am using Excel 2003 at home, but have 2010 available at work if their is an improved functionality that would help.

 

[T. Valko]

I am using Excel 2003 at home, but have 2010 available at work if their is an improved functionality that would help.

Try this...

Data on Sheet2:

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

Sheet1 A2 = some number

This array formula** entered in B2:

=IF(COUNTIF(Sheet2!A2:C5,A2),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.

 

[cyoungtx]

This works for cell B2, but how do I copy the formula down to work for the remaining cells? Ultimately Sheet 1 column A will contain several thousand numbers.

 

[T. Valko]

This works for cell B2, but how do I copy the formula down to work for the remaining cells? Ultimately Sheet 1 column A will contain several thousand numbers.

Make the row references to the data table on sheet2 absolute like this:

=IF(COUNTIF(Sheet2!A$2:C$5,A2),INDEX(Sheet2!A$1:C$1,MAX(IF(Sheet2!A$2:C$5=A2,COLUMN(Sheet2!A$2:C$5)))-COLUMN(Sheet2!A$2)+1),"")

 

[cyoungtx]

Wow, that works! One last question and I think this is a go, how do I change the sheet 2 reference columns on the sheet 1 formula? Say I need 7 color reference columns and I want the entire column referenced against sheet 1 column A.

 

[T. Valko]

Wow, that works! One last question and I think this is a go, how do I change the sheet 2 reference columns on the sheet 1 formula? Say I need 7 color reference columns and I want the entire column referenced against sheet 1 column A.

Not sure what you mean.

Adjust the references to sheet2 as needed but avoid using the entire columns as references since you can't do that in array formulas in Excel versions prior to Excel 2007.

 

[cyoungtx]

Say I want use on sheet 2 columns A-H with a different color value for each row and add the search range on the columns to row 700. I just tried to do it myself and it didn't work. Here is what I did..

=IF(COUNTIF(Sheet2!A$2:H$699,A17),INDEX(Sheet2!A$1:H$1,MAX(IF(Sheet2!A$2:H$699=A17,COLUMN(Sheet2!A$2:H$5)))-COLUMN(Sheet2!A$2)+1),"")

This repeated the color value for column F for the rows I tested on Sheet 1.

 

[Jadegirl]

Can you explain how the max is used in the formulas?
Thanks

 

[T. Valko]

Say I want use on sheet 2 columns A-H with a different color value for each row and add the search range on the columns to row 700. I just tried to do it myself and it didn't work. Here is what I did..

=IF(COUNTIF(Sheet2!A$2:H$699,A17),INDEX(Sheet2!A$1:H$1,MAX(IF(Sheet2!A$2:H$699=A17,COLUMN(Sheet2!A$2:H$5)))-COLUMN(Sheet2!A$2)+1),"")

This repeated the color value for column F for the rows I tested on Sheet 1.

Here's a small sample file that demonstrates this.

ZZZcyoungtx.xls 15kb

http://cjoint.com/?AHkpA6yvpXH