Hi,

Try this

=INDEX(Sheet1!$F$3:$F$6,SUMPRODUCT((Sheet1!$C$3:$E $6=Sheet2!B4)*(ROW(Sheet1!$C$3:$C$6)-ROW(Sheet1!C$2))),1)
and copy down

Sheet1!$C$3:$E$6 is the 4 column shown in your question below
Sheet2!B4 contains 100 on another worksheet

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"brownti" <u31540@uwe wrote in message news:9eab699d6296b@uwe...
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.com
http://www.officekb.com/Uwe/Forums.a...tions/200911/1