If a cell is in a range when using multiple ranges

[xhail]

I'm working on a spread sheet and i'm having an issue with this function.

I'm trying to have excel search to see if the value of a cell is in a list that is on another spread sheet.

If I key a number into cell A1 on sheet 1(for example) I would like for Excel to check sheet 2 (look like the one I made below) and see if that number is listed in a goup of 5-6 colums. If that number is found in a colum I would like it to put the word that is in a1,b1,and c1 respectivley.



Example

A B C
Virginia HH Alabama
1477 1711 2002
1478 1812 2011
1480 1818 1817
1490 1717 1617


So On sheet 1 in cell A1 if I type in 1812 I would want excel to make the cell A2 to say "HH" since 1812 is in the B:B range.

Thanks for the help!

 

[riclop]

Try:
=OFFSET(Sheet2!A1,0,SUMPRODUCT((A1=Sheet2!$A$2:$C$5)*((COLUMN(Sheet2!A2:C5)-COLUMN(Sheet2!A2)))))

As the formula in Sheet1 Cell B1. The input cell is Sheet1 Cell A1. Please note that if you miskey a number the value does not update or indicate that the number you entered is not listed.

 

[T. Valko]

I'm working on a spread sheet and i'm having an issue with this function.

I'm trying to have excel search to see if the value of a cell is in a list that is on another spread sheet.

If I key a number into cell A1 on sheet 1(for example) I would like for Excel to check sheet 2 (look like the one I made below) and see if that number is listed in a goup of 5-6 colums. If that number is found in a colum I would like it to put the word that is in a1,b1,and c1 respectivley.



Example

A B C
Virginia HH Alabama
1477 1711 2002
1478 1812 2011
1480 1818 1817
1490 1717 1617


So On sheet 1 in cell A1 if I type in 1812 I would want excel to make the cell A2 to say "HH" since 1812 is in the B:B range.

Thanks for the help!

Try this...

Book1
	A	B	C
1	Virginia	HH	Alabama
2	1477	1711	2002
3	1478	1812	2011
4	1480	1818	1817
5	1490	1717	1617
Sheet2

Sheet1 A2 = 1812

Enter this array formula** 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),"Not Found")

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