• 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

liabilityquek

New Member
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

bosco_yip

Excel Ninja
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

liabilityquek

New Member
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

bosco_yip

Excel Ninja
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

liabilityquek

New Member
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)?
 
Top