Uneven column lookup

[brownti]

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!

 

[barry houdini]

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!

If you have the table in sheet 1 with numbers in A1:C4 and "Z1" etc in
D14 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$4,MIN(IF(Sheet1!A$1:C
$4=A1,ROW(Sheet1!D$1$4)-ROW(Sheet1!D$1)+1))),"No Match")

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

regards, barry

 

[Ashish Mathur]

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 via OfficeKB.com]

Both work perfect! Thank you very much

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

I have a table that looks like the following:

[quoted text clipped - 28 lines]

Thanks for any input!

 

[Ashish Mathur]

You are welcome

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

Both work perfect! Thank you very much

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

I have a table that looks like the following:

[quoted text clipped - 28 lines]

Thanks for any input!