Doing a VLOOKUP (probably using the INDEX and MATCH function), withboth vertical and horizontal valu

[Mike C]

I am trying to create a function that will pull in data from a 2nd
spreadsheet. Typically, I use the index and match function to do so.

However, in this case, I am trying to do a lookup based on a value
above (i.e., horizontal) and a value to the right (i.e., vertical) of
the cell in which the formula will be placed. Additionally, the sheet
from which I am pulling is similarly laid out.

To Provide an example.

Lookup Table

Months (horizontal) Jan Feb Mar Apr Etc
Names(vertical)
Jeff
Eric 5
Steve 8
John 4


Table with formula
Months (horizontal) Jan Feb Mar Apr Etc
Names
Steve
Jeff
Dave
Eric

So the question is, what formula can I use in the "Table with formula"
to pull in the numbers from the Lookup Table?

Thanks very much for any suggestions!

 

[FrankWood]

You could do this with al slight modification to your lookup table and an
embedded Hlookup to figure out what column# to return.

First add a row (you can hide it later if necessary) under the months to
indicate the correct column number. It would look similar to this:

Jan Feb Mar
2 3 4
Jeff
Eric 5
Steve 8
John 4

Then use a formula similar to this:
=VLOOKUP($A2,$A$28:$M$32,HLOOKUP(B$1,$B$26:$M$27,2,FALSE),FALSE)

Assuming the A2 is “Jeff†and A28:M32 is the table1 and B1=â€Jan†and B27:M27
is Jan-Dec with Row 2 as the Column # to return to the Vlookup.

Hope that helps.

Frank

 

[Pete_UK]

Assuming your lookup table is in Sheet1, then in cell B3 (I think,
from your example) of Sheet2 you would have this:

=INDEX(Sheet1!$B$3:$M20,MATCH($A3,Sheet1!$A$3:$A$20,0),MATCH(B
$1,Sheet1!$B$1:$M$1,0))

I've assumed you have data down to row 20, so adjust if necessary.
Then you can copy this across and down as required.

Hope this helps.

Pete