D

#### Dave K

Hello, I have a list of unique values in Column A in sheet 1. In
sheet2, I am have data laid out in a table with row headers and column

I am trying to come up with a formula that will display the value that
intersects in sheet 2, based on the combined column header and row

So for example.

Sheet 2
Jeff TN Nashville 200
Tim FL Miami 155
Eric GA Atl 225

Sheet 1
Column A Column B
JeffCity Need Formula to Display "Nashville"
TimState Need Formula to Display "FL"

Any suggestion would be appreciated...even if i need to go through
manual steps to produce a simple dragdown formula in Sheet 1.

Thanks!

C

#### Cimjet

Hi Dave
If you can place in sheet1 columnA the name and in column B the State or City or
weight
This formula will work for you.
=INDEX(Sheet2!B24,MATCH(Sheet1!A2,Sheet2!A2:A4,0),MATCH(Sheet1!B2,Sheet2!\$B\$1:\$D\$1,0))
Now you need to adjust the range, my sample is only 3 lines.
Let me know if you can separate the name from the rest.
Cimjet

C

#### Cimjet

I just notice that if you want to copy down, make all range absolute.
=INDEX(Sheet2!\$B\$2:\$D\$4,MATCH(Sheet1!A2,Sheet2!\$A\$2:\$A\$4,0),MATCH(Sheet1!B2,Sheet2!\$B\$1:\$D\$1,0))
If you can't separate Jeff from City this would work but can't be copied down,
you need to adjust it for each row
=INDEX(Sheet2!\$B\$2:\$D\$4,MATCH(LEFT(Sheet1!A5,4),Sheet2!\$A\$2:\$A\$4,0),MATCH(RIGHT(Sheet1!A5,4),Sheet2!\$B\$1:\$D\$1,0))
Cimjet