• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Vlookup with multiple return values

Hi All

with regards to my attachment, I have 2 sets of invoice no data with their respective cost Centers; DGW file and ZAP file.

there are multiple duplicate invoices numbers as each invoices is mapped to their respective cost Centers for these two sets of data and I would like to do a check the cost Centers in the DGW file is correctly reflected from the ZAP file. I tried using the index and small formula and didn’t display the results I want.

please assist, much appreciated.
 

Attachments

  • Book1.xlsx
    9.1 KB · Views: 3
  • Book1 2.xlsx
    436.2 KB · Views: 5
Try............

In "Sheet DGW file" C2, revised array formula ( highlight in red color) copied down :

=IFERROR(INDEX('ZAP file'!$B$2:$B$31003,SMALL(IF('DGW file'!$A2='ZAP file'!$A$2:$A$31003,ROW('ZAP file'!$A$2:$A$31003)-MIN(ROW('ZAP file'!$A$2:$A$31003))+1,""),COUNTIF(A$2:A2,A2))),"")

Regards
Bosco
 

Attachments

  • check DGW file.xlsx
    452.8 KB · Views: 10
Try............

In "Sheet DGW file" C2, revised array formula ( highlight in red color) copied down :

=IFERROR(INDEX('ZAP file'!$B$2:$B$31003,SMALL(IF('DGW file'!$A2='ZAP file'!$A$2:$A$31003,ROW('ZAP file'!$A$2:$A$31003)-MIN(ROW('ZAP file'!$A$2:$A$31003))+1,""),COUNTIF(A$2:A2,A2))),"")

Regards
Bosco
Thanks Bosco, is it possible to vlookup with multiple criteria, for example in this case the invoice no and the amount would be the criteria and thus displaying the respective Cost Centers from the ZAP file worksheet to the DGW file.
 

Attachments

  • Vlookup with multiple criteria displaying multiple results.xlsx
    10.7 KB · Views: 3
Thanks Bosco, is it possible to vlookup with multiple criteria, for example in this case the invoice no and the amount would be the criteria and thus displaying the respective Cost Centers from the ZAP file worksheet to the DGW file.
Try.......

In "DGW file" sheet D2, copied down :

=LOOKUP(1,0/('ZAP file'!A$2:A$29=A2)/('ZAP file'!C$2:C$29=C2),'ZAP file'!B$2:B$29)

Regards
Bosco
 

Attachments

  • Vlookup with multiple criteria displaying multiple results(BY).xlsx
    13.5 KB · Views: 8
Hi
Try.......

In "DGW file" sheet D2, copied down :

=LOOKUP(1,0/('ZAP file'!A$2:A$29=A2)/('ZAP file'!C$2:C$29=C2),'ZAP file'!B$2:B$29)

Regards
Bosco
Bosco, if I have another criterion to lookup to for example “invoice description “, do I add an additional “/“ sign after “( ZAP file’!C$2:C$29=C2)?
 
Back
Top