Advanced Vlookup Formula

  • Thread starter Thread starter Dave K
  • Start date Start date
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
headers.

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
header.

So for example.

Sheet 2
Column Headers
Row Headers State City Weight
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!
 
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!B2:D4,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
 
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
 
Back
Top