Need to Use VLOOKUP alongwith another function to get "Units Shipped" value for each month

**raviraval86** · 12-19-2019, 05:19 PM

Hi, I have 2 sets of files.
1. Monthly Cost - Cost to ship to particular city per month.
2. Monthly Units - Units shipped to particular city per month.

As the city name appears on files multiple times (1 time for each month) I am not able to run simple VLOOKUP formula to get "Units shipped" per city per month in my "Monthly - Cost Per Unit" file.

I don't want to use CONCATENATE as I will not be able to use it for different kind of work.

Can someone please help me which formula I can use combining with VLOOKUP so that I can pull "Units Shipped" values for each city for each month?

Your help is greatly appreciated.
I have attached the files with the thread.

-Ravi

**dosydos** · 12-19-2019, 05:24 PM

Put this in cell D2

array formula so make sure to use CTRL+SHIFT+ENTER

=INDEX('Monthly Cost'!$D$2:$D$10000,MATCH(A2&B2,'Monthly Cost'!$A$2:$A$10000&'Monthly Cost'!$B$2:$B$10000,0))

**dosydos** · 12-19-2019, 05:26 PM

actually a much easier formula that will take up less processing

=SUMIFS('Monthly Cost'!$D:$D,'Monthly Cost'!A:A,A2,'Monthly Cost'!B:B,B2)

**raviraval86** · 12-19-2019, 05:32 PM

Hi dodydos, In which column I put the formula? In my existing file in "Monthly Cost Per Unit" sheet in column D, I want No. Of Units from "monthly Unit" sheet.

Can you please help me to understand the formula?

**dosydos** · 12-19-2019, 05:42 PM

whoops sorry about that, i went after the wrong tab.

=SUMIFS('Monthly Unit'!$D:$D,'Monthly Unit'!A:A,'Monthly - Cost per Unit'!A2,'Monthly Unit'!B:B,'Monthly - Cost per Unit'!B2)

Here you go this is the same sumifs formula except it is looking for the city and month on the monthly Unit tab and returning the total units shipped.
Put this formula in cell D2 of the Monthly - Cost per unit tab
and then drag down

**raviraval86** · 12-19-2019, 05:47 PM

That worked.

Thanks a lot for your help with same.

**CARACALLA** · 12-19-2019, 05:47 PM

Monthly - Cost per Unit

D2=SUMIFS('Monthly Unit'!$D$2:$D$3163,'Monthly Unit'!$C$2:$C$3163,'Monthly - Cost per Unit'!$C2,'Monthly Unit'!$B$2:$B$3163,'Monthly - Cost per Unit'!$B2,'Monthly Unit'!$A$2:$A$3163,'Monthly - Cost per Unit'!$A2)

copy down

**dosydos** · 12-19-2019, 05:48 PM

You are welcome, and Thanks for the Rep

Need to Use VLOOKUP alongwith another function to get "Units Shipped" value for each month

LinkBack

Thread Tools

Rate This Thread

Display