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

M

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

F

#### 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

P

#### 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