VLookup - One lookup, many values

[Sue]

I am trying to create a vlookup formula that will lookup one value and
return many results. I have a spreadsheet that contains Sales Reps and all
the accounts each Sales Rep has. Worksheet 1, for example, will have column
A with all the different reps - REP1, REP2, REP3, etc., none sorted in any
particular order, column B will have the name of their account - Joe's
Diner, Cat Motel, Inc, Bob's Carwash, etc., and Column C will have the
monthly sales volume for that account - $23,232, $15,875, $4,222, etc.

In Worksheet 2, I have a particular Rep that I want to lookup all the
accounts for in spreadsheet 1. Using the Rep name as the lookup value, I
want it to return all the accounts that match that Rep's name.

Worksheet 1 - (named range A1:C6 = Accounts)
A B C
1 REP1 Joe's Diner $23,232
2 REP2 Cat Motel, Inc. $15,875
3 REP3 Bob's Carwash $ 4,222
4 REP2 Absolute Credit $52,777
5 REP3 Jack's Towing $90,113
6 REP2 Smith Consulting $42,549

Worksheet 2
A B C
1 REP2
...
...
7 =vlookup($A$1, Accounts, 2, false) - Here I would like it to return "Cat
Motel, Inc."
8 =vlookup($A$1, Accounts, 2, false) - Here I would like it to return
"Absolute Credit"
9 ......

Instead, it is returning only the first value it finds - "Cat Motel, Inc."
and logically, that is correct. I just don't know how to manipulate the
formula to get the results I need.

Any help is appreciated.

Sue

 

[Dan Hulgin]

Sue, I have the same problem, so if someone has the solution, you get
the satisfaction of helping two people with one answer

 

[Frank Kabel]

Hi
try the following array formula (entered with CTRL+SHIFT+ENTER):
=INDEX('sheet1'!$B$1:$B$100,SMALL(IF('sheet1'!$A$1:$A$100=$A$1,ROW('she
et1'!$A$1:$A$100)),ROW(1:1)))
and copy this down

 

[Alan Beban]

I tried Frank Kabel's posted formula and got a #DIV/0! error; perhaps I
set up the data incorrectly or copied over the formula incorrectly.

In any event, for a different approach, if the functions in the freely
downloadable file at http://home.pacbell.net are available to your
workbook you might try

=Vlookups(A2,A1:B6,{2,3}) array entered into a two-column range with
enough rows to accommodate the output. It will return the accounts to
the first column and the amounts to the second.

Alan Beban

 

[Frank Kabel]

Hi Alan
a #DIV/0 error is the last thing I would expect for this formula
(there's no division). Any chance you have this error in your original
range?

 

[Sue]

Frank,
That formula worked beautifully for me! It's exactly what I was looking for.
Thank you very much.

Sue

 

[Alan Beban]

Hi Alan
a #DIV/0 error is the last thing I would expect for this formula
(there's no division). Any chance you have this error in your original
range?

Indeed I did! I was using the wrong sheet :-(

But after correcting that, I get Joe's Diner in the cell in which I
array enter the formula, and #NUM! in the cells to which I copy it down.
The IF portion of the formula gives 1 in all the cells, and the SMALL
portion of the formula gives 1 in the cell into which it is array
entered and #NUM! when copied down. The only thing that changes when
it's copied is the ROW(1:1).

If you have any interest I'll be happy to email the worksheet.

Alan Beban

 

[Frank Kabel]

Hi Alan
now I'm of course interested. Just send me the sheet to
frank[dot]kabel[at]freenet[dot]de

 

[Karen Stewart]

Herr Kabel, you just saved me two hours a day of copy/pastes and their
attendant occasional errors. I heap 10,000 kisses on your wonderful brow.
Thank you, thank you, thank you, thank you.

Karen Stewart
"Sacred cows make the tastiest hamburgers"

 

[NMP]

Frank,

Did you ever come up with a solution to Alan's problem. I am having the
same problem.

Thanks!

Hi Alan
now I'm of course interested. Just send me the sheet to
frank[dot]kabel[at]freenet[dot]de
--
Regards
Frank Kabel
Frankfurt, Germany

Indeed I did! I was using the wrong sheet :-(

But after correcting that, I get Joe's Diner in the cell in which I
array enter the formula, and #NUM! in the cells to which I copy it down.
The IF portion of the formula gives 1 in all the cells, and the SMALL
portion of the formula gives 1 in the cell into which it is array
entered and #NUM! when copied down. The only thing that changes when
it's copied is the ROW(1:1).

If you have any interest I'll be happy to email the worksheet.

Alan Beban

 

[Bob Phillips]

NMP,

Please see http://tinyurl.com/ca9q7

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[NMP]

Thanks for the info, Bob. So sorry to hear. Sounds like he was a great guy!

Do you think my best option, then is to start a new post? Would you happen
to know the answer to Alan's problem?

NMP

 

[Bob Phillips]

Hopefully, Alan will pick up the thread.

Bob

Thanks for the info, Bob. So sorry to hear. Sounds like he was a great guy!

Do you think my best option, then is to start a new post? Would you happen
to know the answer to Alan's problem?

NMP

 

[Alan Beban]

Thanks for the info, Bob. So sorry to hear. Sounds like he was a great guy!

Do you think my best option, then is to start a new post? Would you happen
to know the answer to Alan's problem?

NMP

I'm having difficulty finding the original thread (and thus identifying
the problem). Perhaps a new post would in fact help.

Alan Beban

 

[NMP]

Alan,

Did you ever figure out the problem? I am having the same problem.

Thanks!

 

[Alan Beban]

Alan,

Did you ever figure out the problem? I am having the same problem.

Thanks!

:

I guess when I suggested a new thread I meant a restatement of the
problem. I don't know what problem is being referred to.

Alan Beban

 

[Bob Phillips]

If it helps Alan, here is a link to the original thread that Frank and
yourself participated in http://tinyurl.com/92hem

From this, I concluded that Frank and yourself had an offline conversation.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Alan Beban]

If it helps Alan, here is a link to the original thread that Frank and
yourself participated in http://tinyurl.com/92hem

From this, I concluded that Frank and yourself had an offline conversation.

I'm afraid I've had to reinstall Windows since then and therefore don't
have emails going back that far, and I don't recall the exchange. For
what it's worth, I still have the same problem with Frank Kabel's posted
formula. And again for what it's worth, with the functions from the
freely downloadable file at http:/home.pacbell.net/beban available to my
workbook, when I array enter the following into Sheet2!D1:F1 and copy down

=TRANSPOSE(Vlookups(A1,Sheet2!$A$1:$C$6,2))

I get

Joe's Diner Joe's Diner Joe's Diner
Cat Motel, Inc Absolute Credit Smith Consulting
Bob's Carwash Jack's Towing #N/A
Cat Motel, Inc Absolute Credit Smith Consulting
Bob's Carwash Jack's Towing #N/A
Cat Motel, Inc Absolute Credit Smith Consulting

Alan Beban

 

[Alan Beban]

Alan,

Did you ever figure out the problem? I am having the same problem.

Thanks!

Perhaps a better way to get at it is for you to indicate what your basic
data is, and what output you want where.

Alan Beban

 

[NMP]

OK. Here is what I have:

Order Number Feature Description
2012066 20 XEROGRAPHIC WHITE
2012066 BLACK
2012066 POLYWRAPPING
2012066 DRILL
2012066 90 INDEX WHITE
2012401 287
2012401 19 CB BLACK PRINT CANARY
2012401 19 CFB BLACK PRINT CANARY
2012401 EDGE GLUE

What I am trying to do is convert it so I list an order number in one column
and then each feature description associated with that order number in
separate columns in the same row. I have around 530 orders each with
multiple feature descriptions.

Thanks!