Adding vlookup instances

gammaves

New Member
Joined
Mar 20, 2017
Messages
28
Hello, I'm having a vlookup issue, and I'm hoping someone can help me out.

I have the following table. I created a helper column, and I'm using that to vlookup the data that I need.

Data:
Book2
ABCDE
1SiteDeviceDateHelperMetric
2Site Adesktop12/14/22Site A44909desktop379
3Site AMobile12/14/22Site A44909Mobile102
4Site Bdesktop12/14/22Site B44909desktop400
5Site BMobile12/14/22Site B44909Mobile150
Sheet1
Cell Formulas
RangeFormula
D2:D5D2=A2&C2&B2



Lookup:
Book2
ABCDE
8SiteDateDeviceHelperMetric
9Site A12/14/22DesktopSite A44909Desktop379
10Site A12/14/22MobileSite A44909Mobile102
11Site B12/14/22DesktopSite B44909Desktop400
12Site B12/14/22MobileSite B44909Mobile150
Sheet1
Cell Formulas
RangeFormula
D9:D12D9=A9&B9&C9
E9:E12E9=VLOOKUP(D9,$D$2:$E$5,2,0)


The complication:
We introduced another dimension to our data - Operating System - which adds 2 rows per device (ios/android for mobile, windows/mac for desktop)

We want to have it so that the Desktop device only has 1 row in the reporting, which is the sum of Windows + Mac. We want Mobile to be broken out by Android / iOS in 2 rows.
The desired outcome:
Book2
ABCDE
1SiteDeviceDateOSMetric
2Site Adesktop12/14/22Desktop379
3Site AMobile12/14/22Android80
4Site AMobile12/14/22iOS22
5Site Bdesktop12/14/22Desktop400
6Site BMobile12/14/22Android120
7Site BMobile12/14/22iOS30
Sheet1


I have no problem using the helper column for Mobile. The problem is that if I were to do a vlookup for desktop, it will find the first instance of the device, and return just Mac, and not windows. Is there a way I can have it return the sum?
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Can you post your lookup data?
How's this?

Site Adesktop12/14/22Chrome OS379
Site Adesktop12/14/22Linux102
Site Adesktop12/14/22Macintosh2196
Site Adesktop12/14/22OS/22
Site Adesktop12/14/22Windows3443
Site Adesktop12/15/22Chrome OS339
Site Adesktop12/15/22Linux91
Site Adesktop12/15/22Macintosh2395
Site Adesktop12/15/22Windows3132
Site Adesktop12/16/22Chrome OS280
Site Adesktop12/16/22Linux76
Site Adesktop12/16/22Macintosh2156
Site Adesktop12/16/22Windows2696
Site Adesktop12/17/22Chrome OS76
Site Adesktop12/17/22Linux81
Site Adesktop12/17/22Macintosh2403
Site Adesktop12/17/22OS/22
Site Adesktop12/17/22Playstation 43
Site Adesktop12/17/22Windows1806
Site Adesktop12/18/22(not set)5
Site Adesktop12/18/22Chrome OS122
Site Adesktop12/18/22Linux92
Site Adesktop12/18/22Macintosh2707
Site Adesktop12/18/22Windows2001
Site Amobile12/14/22(not set)3
Site Amobile12/14/22Android16643
Site Amobile12/14/22BlackBerry2
Site Amobile12/14/22iOS10942
Site Amobile12/15/22Android9633
Site Amobile12/15/22BlackBerry2
Site Amobile12/15/22Firefox OS2
Site Amobile12/15/22iOS8522
Site Amobile12/16/22(not set)2
Site Amobile12/16/22Android8335
Site Amobile12/16/22BlackBerry2
Site Amobile12/16/22iOS8677
Site Amobile12/17/22Android10415
Site Amobile12/17/22iOS10986
Site Amobile12/18/22Android10028
Site Amobile12/18/22iOS11742
Site Amobile12/18/22Tizen2
 
Upvote 0
I mean where are you looking up like what is in range: $D$2:$E$5?
 
Upvote 0
So you want Site A total for mobile and so on?
 
Upvote 0
I'd like to have Site A Desktop return the sum of Macintosh + Windows, and then for Site A Mobile to return a row for android, and a row for iOS
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,317
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top