Hi,
when you do a vlookup - =VLOOKUP($B$1,Tbl,3,0) ; it only returns the very
1st id.
But under owner ABC, i have few Ids to return...

there are 3 lines in the source data for owner ABC
but i only want it to return 2 lines, as one of the id - 1234 is duplicate
(appearing twice)....

--
nikko


"Harlan Grove" wrote:

nikko wrote...
....
Desired results:-
Spreadsheet 1: Owner = ABC
Return the results only for owner ABC in below format:-
ID Lic Mnt
1234 $10 $25
4567 - $50

Spreadsheet 2:
Owner Values ID Product
ABC $10 1234 Lic
ABC $50 4567 Mnt
EFD $60 4569 Mnt
EFD $50 7893 mnt
GCH $30 2456 Mnt
GEH $20 5647 mnt
EFH $35 2434 mnt
ABC $25 1234 Mnt
....

If the table in Spreadsheet 2 (including the row of column headings)
were named Tbl, and if the results began in cell A1 in Spreadsheet 1,

A1:
Owner

B1:
ABC

A3:
ID

B3:
Lic

C3:
Mnt

A4:
=VLOOKUP($B$1,Tbl,3,0)

B4 [array formula]:
=IF(COUNT(1/(INDEX(Tbl,0,3)=$A4)/(INDEX(Tbl,0,4)=B$3)),
INDEX(Tbl,MATCH(1,(INDEX(Tbl,0,3)=$A4)*(INDEX(Tbl, 0,4)=B$3),0),2),"-")

Fill B4 right into C4.

A5 [array formula]:
=INDEX(Tbl,MATCH(1,(INDEX(Tbl,0,1)=$B$1)
*(COUNTIF(A$4:A4,INDEX(Tbl,0,3))=0),0),3)

Fill B4:C4 down into B5:C5, then fill A5:C5 down as far as needed.

Note: these formulas recalculate V E R Y S L O W L Y !

You may be better off using a pivot table for this.