Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Edith F
 
Posts: n/a
Default 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?
  #2   Report Post  
bj
 
Posts: n/a
Default

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?

  #3   Report Post  
Alan Beban
 
Posts: n/a
Default

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
  #4   Report Post  
Bernd Plumhoff
 
Posts: n/a
Default

Hi Edith,

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

HTH,
Bernd
  #5   Report Post  
Harlan Grove
 
Posts: n/a
Default

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.



  #6   Report Post  
Harlan Grove
 
Posts: n/a
Default

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.

  #7   Report Post  
Alan Beban
 
Posts: n/a
Default

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
  #8   Report Post  
Harlan Grove
 
Posts: n/a
Default

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.

  #9   Report Post  
Alan Beban
 
Posts: n/a
Default

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
  #10   Report Post  
Harlan Grove
 
Posts: n/a
Default

"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




  #11   Report Post  
Harlan Grove
 
Posts: n/a
Default

"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.


  #12   Report Post  
Alan Beban
 
Posts: n/a
Default

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
  #13   Report Post  
Harlan Grove
 
Posts: n/a
Default

"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.


  #14   Report Post  
Alan Beban
 
Posts: n/a
Default

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.
  #15   Report Post  
Harlan Grove
 
Posts: n/a
Default

"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?




  #16   Report Post  
Alan Beban
 
Posts: n/a
Default

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.
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
how do I use vlookup for multiple occurrences of the same value bj Excel Worksheet Functions 0 April 27th 05 10:43 PM
Array Function with VLOOKUP CoRrRan Excel Worksheet Functions 15 April 8th 05 05:54 PM
make a vlookup using a variable path Alex St-Pierre Excel Worksheet Functions 1 March 3rd 05 12:54 AM
vlookup data hidden within worksheet Excel Worksheet Functions 0 January 26th 05 01:09 PM
Vlookup info being used without vlookup table attached? Excel Worksheet Functions 0 January 25th 05 11:43 AM


All times are GMT +1. The time now is 11:11 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"