CODEWITHSUNDEEP
google-sheetssumunique
Mohamad Khuddus
Mohamad Khuddus

Reputation: 21

In Google sheets, How to do Vlookup unique data from multiple column

My Vlookup formula is the following currently:

(Weekly Consolidated Sheet)

Column C:1 =iferror(arrayformula(if(D:D="Item No.","Document No.",('For NAV'!C3:C))),"") Column D:1

=iferror(arrayformula(if(A:A="Posting Date","Item No.",Unique('For NAV'!D2:D))),"") Column I:1

=iferror(arrayformula(if(A:A="Posting Date","Quantity",sumif('For NAV'!D:D,D:D,'For NAV'!I:I))),"")

Above Image The Spreadsheet

I would want to consolidate data from "For NAV" sheet into "Weekly Consolidated" Sheet.

"For NAV" Sheet has duplicate Item No. which I want to make it Unique and Sum the total Quantity (Column I)

Now that the document number seems to be the main Criteria I am stuck.

The following is what I need:

Main Criteria = Document No

Example:

ADW 01-07/03

-Item number which has same document number should consolidate its own Unique item number and sum the Quantity

ADW 08-14/03

-Item number which has same document number should consolidate its own Unique item number and sum the Quantity

Other columns is OK i can vlookup from "Item Listing" Sheet based on the Item code

Link for the spreadsheet as below:

https://docs.google.com/spreadsheets/d/1SL63pIF35skZjjbFjTRgfsbSYHvDr-gRRKciZbYFRUU/edit#gid=1168278681

Upvotes: 0

Views: 505

Answers (1)

JPV
JPV

Reputation: 27242

Try changing your formula in I2 to

=ArrayFormula(iferror({"Quantity";sumif('For NAV'!C3:C&'For NAV'!D3:D,C2:C&D2:D,'For NAV'!I3:I)}))

The way this works is by making a unique string generated from the values on the C and D columns to compare between sheets. In this case you can simply joining them together because the the C column is always the same size.

Upvotes: 1

Related Questions