Advanced Vlookup Formula

[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!

 

[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

 

[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

 

[Ron Rosenfeld]

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!

On Sheet 1, can you put a <space> between Jeff and City so it looks like "Jeff City" (without the quotes) ??

If so, and if your data starts in sheet2 with the upper left blank cell being A1, you can use this formula:

Sheet1!B1: =HLOOKUP(MID(A1,FIND(" ",A1)+1,99),Sheet2!$1:$100, MATCH(LEFT(A1,FIND(" ",A1)-1),Sheet2!$A:$A,0),FALSE)

and fill down as far as needed.

Notes: The Table_array argument in the HLOOKUP should be changed to encompass the maximum size of the entire table.

Also, if you have a version of Excel prior to 2007, you may need to change the $A:$A reference in the MATCH function to a range smaller than an entire column. For example: $A$1:$A$65535.

You could use dynamic references, but I don't know if you save much.