Using Vlookup find multiple result

[Hardeep_kanwar]

This is my question: Thia data is in sheet 1
Acct # Account Name Invoice # Date Amount Type
1 Osama 157 01/01/08 1000.00 INV
1 Osama 157 01/02/08 -990.00 Pay
1 Osama 158 01/03/08 800.00 INV

In Sheet2
Account Name
Osama
Osama
Osama

Now i want Invoice no. Date , and Amount

I knew about vlookup but i have to put three times this function

Any help will be appriciate

Hardeep kanwar

 

[Mike H]

Hi,

Assuming
Account name Column B
Invoice Column C
Amount Column E
The name you are looking for in A1 of another sheet

Try this for the invoice numbe
=IF(ROWS(B$2:B2)<=COUNTIF(Sheet1!$B$2:$B$21,$A$2),INDEX(Sheet1!$C$2:$C$21,SMALL(IF(Sheet1!$B$2:$B$21=$A$2,ROW(Sheet1!$B$2:$B$21)-ROW($A$2)+1),ROWS(B$2:B2))),"")

And this for the amoun
=IF(ROWS(D$22)<=COUNTIF(Sheet1!$B$2:$B$21,$A$2),INDEX(Sheet1!$E$2:$E$21,SMALL(IF(Sheet1!$B$2:$B$21=$A$2,ROW(Sheet1!$B$2:$B$21)-ROW($A$2)+1),ROWS(D$22))),"")

Drag each formula down to get the second and subsequent matches

Mike