On Nov 7, 1:42*pm, "brownti" <u31540@uwe wrote:
I have a table that looks like the following:

100 * * 200 * * 300 * * Z1
400 * * 500 * * * * * * * *Z2
600 * * * * * * * * * * * * * Z3
700 * * 800 * * 900 * * Z4

Then on another worksheet the following:
100
200
300
400
500
600
700
800
900

I would like to do some sort of lookup to find which Z goes with each number.
So that my final table would be:
100 * Z1
200 * Z1
300 * Z1
400 * Z2
500 * Z2
600 * Z3
700 * Z4
800 * Z4
900 * Z4

Thanks for any input!

--
Message posted via OfficeKB.comhttp://www.officekb.com/Uwe/Forums.aspx/excel-functions/200911/1

If you have the table in sheet 1 with numbers in A1:C4 and "Z1" etc in
D1:D4 then try this formula in another sheet A2 with lookup value in
A1

=IF(COUNTIF(Sheet1!A$1:C$4,A1),INDEX(Sheet1!D$1:D$ 4,MIN(IF(Sheet1!A$1:C
$4=A1,ROW(Sheet1!D$1:D$4)-ROW(Sheet1!D$1)+1))),"No Match")

confirmed with CTRL+SHIFT+ENTER and copied down the column

regards, barry