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

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

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

Both work perfect! Thank you very much

Ashish Mathur wrote:
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!

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200911/1

You are welcome

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"brownti via OfficeKB.com" <u31540@uwe wrote in message
news:9edca45573480@uwe...
Both work perfect! Thank you very much

Ashish Mathur wrote:
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!

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200911/1