# Microsoft Office Application Help - Excel Help forum > Excel Formulas & Functions >  >  how do I use vlookup for multiple occurrences of the same value

## bj

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

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?

----------


## Alan Beban

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_reference) 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_reference), Row(A1))
filled down as far as required.

Alan Beban

----------


## Bernd Plumhoff

Hi Edith,

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

HTH,
Bernd

----------


## Harlan Grove

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.

----------


## Harlan Grove

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.

----------


## Alan Beban

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

----------


## Harlan Grove

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.

----------


## Alan Beban

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

"Harlan Grove" <hrlngrv@aol.com> 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

----------


## Harlan Grove

"Alan Beban" <unavailable@no.com> 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.

----------


## Alan Beban

Harlan Grove wrote:
> "Alan Beban" <unavailable@no.com> 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

----------


## Harlan Grove

"Alan Beban" <unavailable@no.com> 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.

----------


## Alan Beban

Harlan Grove wrote:
> "Alan Beban" <unavailable@no.com> 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.

----------


## Harlan Grove

"Alan Beban" <unavailable@no.com> 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?

----------


## Alan Beban

Harlan Grove wrote:
> "Alan Beban" <unavailable@no.com> 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.

----------


## viteshsingla

Vlookup provides us with an option of searching for the first string only. But in case we need to go for the second or third or may be last entry of the value we can use a combination of few other functions of excel.

Below is the formula, which we can use for the above purpose;
(as this is an array formula so please hold ctrl and shift key while entering it)
=index($a$1:$b$25,large(($a$1:$a$25=e2)*row($a$1:$a$25),countif($a$1:$a$25,e2)-f2+1),2)

attached is the file for the same.

----------


## jonmiller

You people are brilliant! I am having a hard time with a formula. I want to use a VLOOKUP to return the sum of multiple occurrences of the same value. For example...
Tom     3
Jerry    4
Tom     3
Mike    5
Jerry    2
Tom    6
So, I am trying to do a lookup in one sell searching for Tom and instead of only returning 3 (because it is the first occurrence), I would like it to add 3,3,6 and return 12. Is there a way to do this?

----------


## paulmli

Modeling for a film. Need to essentially find a film's genre in a table and match the given box office level to a set range of box office levels in order to determine marketing spend for a film

----------

