how do I use vlookup for multiple occurrences of the same value
 

I am using the vlookup function to check a table which may or may not have
multiple rows for the same value of the column I am using to select. So far,
everything I have tried keeps giving me the first occurrence it finds. Do I
need to add additional parameters or should I be using something other than
vlookup?

I'm sorry that I didn't answer your question.
vlookup will only show the first occurance . You will need something more.

"Edith F" wrote:

I am using the vlookup function to check a table which may or may not have
multiple rows for the same value of the column I am using to select. So far,
everything I have tried keeps giving me the first occurrence it finds. Do I
need to add additional parameters or should I be using something other than
vlookup?

Edith F wrote:
I am using the vlookup function to check a table which may or may not have
multiple rows for the same value of the column I am using to select. So far,
everything I have tried keeps giving me the first occurrence it finds. Do I
need to add additional parameters or should I be using something other than
vlookup?

If you have the functions in the freely downloadable file at
http:/home.pacbell.net/beban available to your workbook you can use the
VLookups function:

=VLookups(lookup_value,Lookup_table,column_referen ce) array entered into
enough vertical cells to accommodate the number of occurrences of
lookup_value. Or, to avoid array entering:

=Index(VLookups(lookup_value,Lookup_table,column_r eference), Row(A1))
filled down as far as required.

Alan Beban

Hi Edith,

maybe my function vlookupall() at
http://www.sulprobil.com/html/vlookupall.html can help you.

HTH,
Bernd

Alan Beban wrote...
....
If you have the functions in the freely downloadable file at
http:/home.pacbell.net/beban available to your workbook you can use
the
VLookups function:
....

Yes, but this could be done with built-in formulas. If the source range
were named Tbl, the lookup value were in cell G1, and the topmost
result in cell H1 with other results to appear below it in col H, the
following formulas would work.

H1:
=VLOOKUP(G1,Tbl,2,0)

H2 [array formula]:
=IF(COUNTIF(INDEX(Tbl,0,1),G$1)ROW()-ROW(H$1),
OFFSET(Tbl,SMALL(IF(INDEX(Tbl,0,1)=G$1,ROW(Tbl)-CELL("Row",Tbl)),
ROW()-ROW(H$1)+1),1,1,1),"")

Fill H2 down as far as needed.

Alan Beban wrote...
....
If you have the functions in the freely downloadable file at
http:/home.pacbell.net/beban available to your workbook you can use
the
VLookups function:
....

Yes, but this could be done with built-in functions. If the source
range were named Tbl, the lookup value were in cell G1, and the topmost
result in cell H1 with other results to appear below it in col H, the
following formulas would work.

H1:
=VLOOKUP(G1,Tbl,2,0)

H2 [array formula]:
=IF(COUNTIF(INDEX(Tbl,0,1),G$1)ROW()-ROW(H$1),
OFFSET(Tbl,SMALL(IF(INDEX(Tbl,0,1)=G$1,ROW(Tbl)-CELL("Row",Tbl)),
ROW()-ROW(H$1)+1),1,1,1),"")

Fill H2 down as far as needed.

Harlan Grove wrote:
Alan Beban wrote...
...

If you have the functions in the freely downloadable file at
http:/home.pacbell.net/beban available to your workbook you can use

the

VLookups function:

...

Yes, but this could be done with built-in formulas.

Indeed, as you demonstrated, though it's not clear why that would be
desirable.

The particular formula you provided is slower than the array entered
VLookups formula when the number of recalculations on a sheet gets
relatively large.

I wonder how a user would test where the crossover in speed occurs so
he/she could get some guidance on which works best in his/her
application. I suppose just try them and see if there's a noticeable
difference.

Or, of course, if one just has a predisposition for built-in formulas
without regard for efficiency, then there you have one.

Alan Beban

Alan Beban wrote...
....
The particular formula you provided is slower than the array entered
VLookups formula when the number of recalculations on a sheet gets
relatively large.
....

There are situations in which Excel workbooks can't use any VBA, so
it's good to know how to do certain tasks using no VBA. We may disagree
about this, but IMO it's best to avoid VBA for anything that can be
done compactly with built-in functions and defined names. Note the
fuzzy term 'compactly'.

On the other hand, if recalc performance is absolutely critical, better
to use 2 formulas/cells per each result plus one extra formula/cell.

G2:
=ROWS(Tbl)

H1:
=VLOOKUP(G$1,Tbl,2,0)

I1:
=MATCH(G$1,INDEX(Tbl,0,1),0)

H2:
=INDEX(Tbl,I2,2)

I2:
=MATCH(G$1,OFFSET(Tbl,I1,0,G$2-I1,1),0)

I guarantee you this will run recalc circles around your VLookups
formulas. Benchmark results available upon request.

Harlan Grove wrote:
Alan Beban wrote...
...

The particular formula you provided is slower than the array entered
VLookups formula when the number of recalculations on a sheet gets
relatively large.

...

There are situations in which Excel workbooks can't use any VBA, so
it's good to know how to do certain tasks using no VBA. We may disagree
about this, but IMO it's best to avoid VBA for anything that can be
done compactly with built-in functions and defined names. Note the
fuzzy term 'compactly'.

On the other hand, if recalc performance is absolutely critical, better
to use 2 formulas/cells per each result plus one extra formula/cell.

G2:
=ROWS(Tbl)

H1:
=VLOOKUP(G$1,Tbl,2,0)

I1:
=MATCH(G$1,INDEX(Tbl,0,1),0)

H2:
=INDEX(Tbl,I2,2)

I2:
=MATCH(G$1,OFFSET(Tbl,I1,0,G$2-I1,1),0)

I guarantee you this will run recalc circles around your VLookups
formulas. Benchmark results available upon request.

Which of the formulas, if any, are to be array entered?

Which get copied where to display the output?

Alan Beban

"Harlan Grove" wrote...
....
I2:
=MATCH(G$1,OFFSET(Tbl,I1,0,G$2-I1,1),0)
....

Oops, make that

I2:
=MATCH(G$1,OFFSET(Tbl,I1,0,G$2-I1,1),0)+I1

"Alan Beban" wrote...
Harlan Grove wrote:
....
On the other hand, if recalc performance is absolutely critical, better
to use 2 formulas/cells per each result plus one extra formula/cell.

G2:
=ROWS(Tbl)

H1:
=VLOOKUP(G$1,Tbl,2,0)

I1:
=MATCH(G$1,INDEX(Tbl,0,1),0)

H2:
=INDEX(Tbl,I2,2)

I2:
=MATCH(G$1,OFFSET(Tbl,I1,0,G$2-I1,1),0)
....

I already mentioned I screwed up the I2 formula. It should be

=MATCH(G$1,OFFSET(Tbl,I1,0,G$2-I1,1),0)+I1

Which of the formulas, if any, are to be array entered?

None. You couldn't test to be sure? You're not sufficiently familiar with
Excel to know yourself? Just a rhetorical question for the benefit of other
readers, and you prefer that device to simply stating none of them need to
be entered as array formulas?

Which get copied where to display the output?

I considered my previous response an extension of my response before that,
so implicit to drag the formulas in row 2 down until they return error
values. I must endeavor to remember that you need everything explicit.

Harlan Grove wrote:
"Alan Beban" wrote...

Harlan Grove wrote:

...

On the other hand, if recalc performance is absolutely critical, better
to use 2 formulas/cells per each result plus one extra formula/cell.

G2:
=ROWS(Tbl)

H1:
=VLOOKUP(G$1,Tbl,2,0)

I1:
=MATCH(G$1,INDEX(Tbl,0,1),0)

H2:
=INDEX(Tbl,I2,2)

I2:
=MATCH(G$1,OFFSET(Tbl,I1,0,G$2-I1,1),0)

...

I already mentioned I screwed up the I2 formula. It should be

=MATCH(G$1,OFFSET(Tbl,I1,0,G$2-I1,1),0)+I1


Which of the formulas, if any, are to be array entered?


None. You couldn't test to be sure? You're not sufficiently familiar with
Excel to know yourself? Just a rhetorical question for the benefit of other
readers, and you prefer that device to simply stating none of them need to
be entered as array formulas?

No. You gave me a screwed up formula for I2 and I was just trying to see
why what you provided wasn't working as you suggested it would.


Which get copied where to display the output?


I considered my previous response an extension of my response before that,
so implicit to drag the formulas in row 2 down until they return error
values.


I already did that with the only formula you provided for I2 and it
produced garbage; so yes indeed, I and any one else trying to use what
you provided needed something more explicit. Our fault, of course.

I must endeavor to remember that you need everything explicit.

No; just endeavor to test your stuff before you post it so you won't
have to embarrass yourself by trying to shift the responsibility to me
when it doesn't work.

Alan Beban

"Alan Beban" wrote...
....
No; just endeavor to test your stuff before you post it so you won't
have to embarrass yourself by trying to shift the responsibility to me
when it doesn't work.

I admit my own mistakes. I screwed up the I2 formula, and I didn't test it
(I knew the technique works, but I failed to add the I1 value to I2).
However, did you make a clear statement that it didn't work? No, you babble
some oblique crap about which formula to array-enter and which to copy where
to get the results.

Harlan Grove wrote:
"Alan Beban" wrote...
...

No; just endeavor to test your stuff before you post it so you won't
have to embarrass yourself by trying to shift the responsibility to me
when it doesn't work.


I admit my own mistakes. I screwed up the I2 formula, and I didn't test it
(I knew the technique works, but I failed to add the I1 value to I2).
However, did you make a clear statement that it didn't work? No, you babble
some oblique crap about which formula to array-enter and which to copy where
to get the results.



How noble of you to admit your own mistakes while refusing to take
responsibility for the confusion they directly caused. I was simply
trying to exhaust the possibility that there was something I wasn't
understanding about the use of your formulas before saying that it was
the formulas themselves that were screwed up. But that seems to be a
little too subtle for you to comprehend.

"Alan Beban" wrote...
....
. . . I was simply
trying to exhaust the possibility that there was something I wasn't
understanding about the use of your formulas before saying that it was
the formulas themselves that were screwed up. But that seems to be a
little too subtle for you to comprehend.

Unadulterated BS.

The formula didn't work. Presumably you figured that out, but did you want
to say that? Much less venture a fix?

Harlan Grove wrote:
"Alan Beban" wrote...
...

. . . I was simply
trying to exhaust the possibility that there was something I wasn't
understanding about the use of your formulas before saying that it was
the formulas themselves that were screwed up. But that seems to be a
little too subtle for you to comprehend.


Unadulterated BS.

The formula didn't work. Presumably you figured that out, but did you want
to say that? Much less venture a fix?



At the time I had *not* figured that out. I was still trying to figure
out whether it was that the formulas could not work (which would imply
that without so advising the users you posted without testing--not the
most likely probability in my mind at the time), or that I was applying
the formulas inappropriately; hence the questions about array entering
and copying. I wouldn't consider venturing a "fix" unless and until I
knew it was the formulas themselves and not my particular attempt to
apply them that was the problem.

But that's OK; rant on, it's instructive for the users.