vlookup when data is not all in same row

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a spreadsheet that outputs with a sales person name in A1 but the info
that I need from that name (using Vlookup) is in B6. The next sales persons
name is in C1, info in D6 and so on. Currently I'm opening the spreadsheet
and inserting a cell in the first row to drop A1 to B1. What I would like is
a formula with Vlookup that will pull the info from B6 if it finds a match in
A1. Is this possible?
 
Brad,

Impossible to tell for sure what you are actually doing since your seem to
use rows and columns interchangeably, but one of these formulas should give
you a starting point.

If your sales persons' names go down the column (all in the same column):
=INDEX(B:B,MATCH("Brad",A:A,FALSE)+5)
If your sales persons' names go across the row (all on the same row):
=INDEX(6:6,1,MATCH("Brad",2:2,FALSE)+1)

HTH,
Bernie
MS Excel MVP
 
to be more specific, I have a master spreadsheet with a sales person ID in A5
(AAB) and I want to use that info to go find AAB on a spreadsheet called
"last months orders" and return the dollar amount of his orders. On this
spreadsheet, AAB will be in A14 but the dollar amount is in K15. I think
that the index function is going to work but how do I write it to get the
info from the other spreadsheet that I want?
 
Brad,

When you say "Spreadsheet", do you mean a separate worksheet, or do you
mean a separate file (some people use the terms interchangeably)?

Also, do you want the formula on the master spreadsheet? And do the names
run down the column or across the row?

Bernie
 
I mean separate file. I would like the formula on the master spreadsheet and
the names run down the column A.
 
Brad,

If the name of the sales person is in Cell A1 on the same sheet with the
formula, then you would use

=INDEX('[last months orders.xls]Order Details'!$K:$K,MATCH(A1,'[last months
orders.xls]Order Details'!$A:$A,FALSE)+1)

This example is for a workbook named "last months orders.xls", with the
sheet with the data named "Order Details"

HTH,
Bernie
MS Excel MVP
 
Back
Top