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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Back
Top