vlookup more than one exact match

[Guest]

For vlookup, If there are two or more values in the first column of
table_array that match the lookup_value, the first value found is used. I
have several matches for each lookup value. Which function/functions can i
use to show all the matches under each lookup value.

Thank ou in anticipation!
Russ

 

[Guest]

Hi Russ

Vlookup works with a unique list. You should only have 1 instance of a
unique name in that list, and if I am not mistaken, it should also be sorted,
for VLookup to work properly. If you use FALSE in your formula, it will then
find an exact match. If you use TRU, it will find the closest match. Why
not remove the duplicates? If however you are trying to find out how many
instances of a name there are in a given list, use Data|Autofilter, and
filter for the specific name. You will then see all the instances of that
name.

 

[Bernie Deitrick]

Russ,

Usually, data filters will show you what you want.

However, if you need to extract the values to another table, you can use an array formula (entered
using Ctrl-Shift-Enter) like:

=INDEX($E$1:$E$10,LARGE(($D$1:$D$10="Test")*ROW($D$1:$D$10),ROW(D1)))

Where E1:E10 has the data you want, D110 has the labels. This formula, as written, will return
the values in the opposite order of how they apear.
Copy down for as many cells as values you expect.

HTH,
Bernie
MS Excel MVP

 

[Harlan Grove]

Kassie wrote...

Vlookup works with a unique list. You should only have 1 instance of a
unique name in that list, and if I am not mistaken, it should also be sorted,
for VLookup to work properly. . . .

Maybe in Lotus 123, but not in Excel. The 4th argument to VLOOKUP
allows for optional exact matching in unsorted first columns, and
there's never been a requirement in either 123 or Excel that each value
in the first column of the table be distinct. You're thinking of DGET.

. . . If you use FALSE in your formula, it will then
find an exact match. If you use TRU, it will find the closest match. Why
not remove the duplicates? . . .

Because not all tables are sensibly designed, and duplicate entries in
the first column don't imply duplicate entries in the other columns. An
example would be names in column 1, transaction dates in column 2,
transaction amounts in column 3. The same person (same name in column
1) could have multiple distinct transactions. There's no duplicate
*RECORDS*, only duplicate names.

 

[Harlan Grove]

Bernie Deitrick wrote...
....

However, if you need to extract the values to another table,
you can use an array formula (entered using Ctrl-Shift-Enter) like:

=INDEX($E$1:$E$10,LARGE(($D$1:$D$10="Test")*ROW($D$1:$D$10),ROW(D1)))

....

LARGE would return the matches in reversed order. Replace the LARGE
call with a SMALL call to fetch the matches in the original order.
Of course, the reason why you had to use LARGE was that your first
argument to LARGE would return zeros for nonmatches, and you had to
avoid them. Using SMALL would require using an IF call, but since the
formula would need to be array-entered, no big deal.

=INDEX($E$1:$E$10,SMALL(IF($D$1:$D$10="Test",ROW($D$1:$D$10)),
ROWS(D$11)))

 

[Guest]

Hi,

Assuming you have data in range A1:B7 and the value you want data for (from
the range above) in cell A10, type the following array formula
(Ctrl+Shift+Enter)

=IF(ISERROR(INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=$A$10,ROW($A$1:$A$7)),ROW(1:1)),2)),"",INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=$A$10,ROW($A$1:$A$7)),ROW(1:1)),2))

Please keep inmind that your list must start from row 1

Regards,

 

[Harlan Grove]

Ashish Mathur wrote...

Assuming you have data in range A1:B7 and the value you want data for (from
the range above) in cell A10, type the following array formula
(Ctrl+Shift+Enter)

=IF(ISERROR(INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=$A$10,ROW($A$1:$A$7)),
ROW(1:1)),2)),"",INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=$A$10,ROW($A$1:$A$7)),
ROW(1:1)),2))

....

The ISERROr call is unnecessary. If it were intended to display "" when
all matches in column A were exhausted, it'd be more efficient to use

=IF(ROW(1:1)>COUNTIF($A$1:$A$7,$A$10),"",
INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=$A$10,ROW($A$1:$A$7)),ROW(1:1)),2))