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]

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

This expression will return the column number of the referenced range for the column that contains the lookup value and the logical value FALSE for the columns that do not contain the lookup value.

IF(Sheet2!A$2:H$699=A17,COLUMN(Sheet2!A$2:H$5))

This expression returns an array of values but we need to narrow this array down to a single value that is then passed on to the INDEX function. One way to do that is to use the MAX function.

Let's assume this is the table data:

Book1
	A	B	C	D
1	Color1	Color2	Color3	Color4
2	55	84	98	16
3	83	61	57	12
4	37	91	67	51
5	70	98	28	58
Sheet2

If the lookup value was 67 then:

IF(Sheet2!A$2:D$5=A17,COLUMN(Sheet2!A$2:D$5))

Returns this array:

{FALSE,FALSE,FALSE,FALSE;FALSE,FALSE,FALSE,FALSE;FALSE,FALSE,3,
FALSE;FALSE,FALSE,FALSE,FALSE}

In this case the lookup value is found in column C (column number 3) so we need to extract that 3 from the array and pass it to the INDEX function. So, we use the MAX function:

MAX(
{FALSE,FALSE,FALSE,FALSE;FALSE,FALSE,FALSE,FALSE;FALSE,FALSE,3,
FALSE;FALSE,FALSE,FALSE,FALSE})

=3