vlookup when data is not all in same row
 

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

"Brad" wrote in message
...
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?

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?

"Bernie Deitrick" wrote:

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

"Brad" wrote in message
...
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,

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


"Brad" wrote in message
...
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?

"Bernie Deitrick" wrote:

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

"Brad" wrote in message
...
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?

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

"Bernie Deitrick" wrote:

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


"Brad" wrote in message
...
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?

"Bernie Deitrick" wrote:

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

"Brad" wrote in message
...
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,

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

"Brad" wrote in message
...
I mean separate file. I would like the formula on the master spreadsheet
and
the names run down the column A.

"Bernie Deitrick" wrote:

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


"Brad" wrote in message
...
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?

"Bernie Deitrick" wrote:

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

"Brad" wrote in message
...
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?