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

  • Thread starter Thread starter Mike C
  • Start date Start date
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!
 
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
 
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
 
Back
Top