VLOOKUP with more matches

  • Thread starter Thread starter Hans Knudsen
  • Start date Start date
H

Hans Knudsen

Hello
Lookup value 4242424 (lookup in Number and return Vendor)

Number
Vendor

4242424
sk

4242424
us

4242425
sk

4242425
us


4242424 ps

Will someone help me with a formula that will return sk in the first cell (for example C1), us in C2, ps in C3 and so on if there
are more matches.

Hans Knudsen
 
If the functions in the freely downloadable file at
http://home.pacbell.net/beban are available to your workbook, then with
your data in A2:B100:

=VLookups(4242424,A2:B100,2) arrayentered into C1:Cn, where n is at
least large enough to accommodate all the output.

Alan Beban
 
Hej Hans

Here's one way:

The lookup value in C1, data in A2:B12,
enter this array formula in in C2:

=IF(COUNTIF($A$2:$A$12,$C$1)<=ROW()-ROW($C$2),"",
INDEX($B$2:$B$12,MIN(IF(($A$2:$A$12=$C$1)*
(COUNTIF(OFFSET($A$2,,,ROW($A$2:$A$12)-ROW($A$2)+1,1),$C$1)=
ROW()-ROW($C$2)+1),ROW($A$2:$A$12)-ROW($A$2)+1))

To be entered with <Shift><Ctrl><Enter>.

If a startcell other than C2 is chosen (e.g. H1), change

ROW()-ROW($C$2)
to
ROW()-ROW($H$1)
as this expression is used as a counter.

--
Best Regards
Leo Heuser

Followup to newsgroup only please.

Hans Knudsen said:
Hello
Lookup value 4242424 (lookup in Number and return Vendor)

Number
Vendor

4242424
sk

4242424
us

4242425
sk

4242425
us


4242424 ps

Will someone help me with a formula that will return sk in the first cell
Click to expand...
(for example C1), us in C2, ps in C3 and so on if there
 
To Alan Beban and Leo Heuser.
Both solutions work absolutely perfect. Thank you very much to each of you.

Hans Knudsen
 
Thanks for the feedback. It's particularly helpful to get feedback on
the Array Functions.

Alan Beban
 
Hi Leo,

I hesitate to mention this because the OP has posted saying this works
perfectly, but the formula seems to have more left parentheses than
right parentheses. I added two more at the end, but it doesn't seem to
give the advertised result that way, so I'd like to make sure I have the
appropriate formula before checking it anymore.

Thanks,
Alan Beban
 
Hi Alan

You're right! Two right parentheses are missing and
must be added at the end. I guess Hans did that. I
don't understand, why it won't work for you.

It puzzled me, that they were missing because I
copied the formula directly from a cell in an international
macro sheet, but I have now found out, what has
gone wrong.

I work in a Danish version of Excel, and when the formula
is OK, I insert an international macro sheet and copy
the formula to a cell in that sheet to get the formula
translated to English. Then I copy the formula from the
formula bar and paste it to the posting.

It now turns out, that when I click to the right of this
particular formula in the formula bar (to drag to the start
to select it all), the formula wordwrap at another point in
the formula (!), thereby making the last line longer, which
means that the cursor is inserted inside the formula instead of
at the end (this behaviour is not duplicated in an ordinary
sheet). So, what happened was, that I clicked at the end,
the cursor was placed to the left of the two rightmost
parentheses (which I didn't notice), I dragged to the start
of the formula and copied.

It may have occurred in other of my postings (God forbid!),
and I can only hope, that it hasn't caused too much baldness
among the users :-)

Thanks for your posting, Alan! It made it possible for me
to find this "ugly fellow".

In the future I will *always* click at the start of the formula
and drag to the end!
 
Hi Leo,

when I used your formula (which works fine) excel added the two missing
parenthesis
 
Hi Peo

Thanks!
No parentheses are added automatically in Excel 2002,
so apparently this is a new feature in Excel 2003.
 
Sorry Leo. Wrong of me, it adds them after suggesting it,
not automatically.. I believe that is the same as before..
 
Thanks, Leo. With 1,2,3,4,3,3,3,6,7,8,9 in A2:A12, and a thru k in
B2:B12, and 3 in C1, and the following formula array entered into C2:C5:

=IF(COUNTIF($A$2:$A$12,$C$1)<=ROW()-ROW($C$2),"",INDEX($B$2:$B$12,MIN(IF(($A$2:$A$12=$C$1)*(COUNTIF(OFFSET($A$2,,,ROW($A$2:$A$12)-ROW($A$2)+1,1),$C$1)=ROW()-ROW($C$2)+1),ROW($A$2:$A$12)-ROW($A$2)+1))))

it returns c,#N/A,#N/A,#N/A into c2:c5

What am I doing wrong?

Alan Beban
 
I believe this formula is to be array entered into a single cell and then
copied down
 
Alan,

I wonder if you could help me with your VLookups formula?
I have been trying to use your formula to return columns 1 & 3 for
example of the following data:

frog car horse
toad dog cow
frog bike sheep

If I use =VLookups(A3,A3:C5,{1,3}) array entered this works fine -
returning the following:

frog horse
frog sheep

However, if I adapt this to my needs (that is looking up against
dates) the formula returns a #Value error. For example, if I change
the word frog to a date in the data above, I get the same #Value
error.

Does your formula not work for dates or numbers or am I doing
something wrong?

Thanks,

Richard
 
You're doing something wrong, though I can't tell what it is. I
reproduced your

frog horse
frog sheep

example in the same cells of a worksheet, array entering into D4:E5. I
then changed frog two times to 1/3/04 and got

1/3/04 horse
1/3/04 sheep

My column A and D were both formatted Date | 3/14/98

There must be something you aren't telling us about the nature of the
date entries. If you don't track it down and your email address is
included correctly in your posting, post back and I will email you my
email address so you can attach your workbook to an email to me.

Alan Beban
 
Back
Top