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

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

Hi Edith,

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

HTH,
Bernd