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:
Lookup:
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:
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?
I have the following table. I created a helper column, and I'm using that to vlookup the data that I need.
Data:
| Book2 | |||||||
|---|---|---|---|---|---|---|---|
| A | B | C | D | E | |||
| 1 | Site | Device | Date | Helper | Metric | ||
| 2 | Site A | desktop | 12/14/22 | Site A44909desktop | 379 | ||
| 3 | Site A | Mobile | 12/14/22 | Site A44909Mobile | 102 | ||
| 4 | Site B | desktop | 12/14/22 | Site B44909desktop | 400 | ||
| 5 | Site B | Mobile | 12/14/22 | Site B44909Mobile | 150 | ||
Sheet1 | |||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| D2:D5 | D2 | =A2&C2&B2 |
Lookup:
| Book2 | |||||||
|---|---|---|---|---|---|---|---|
| A | B | C | D | E | |||
| 8 | Site | Date | Device | Helper | Metric | ||
| 9 | Site A | 12/14/22 | Desktop | Site A44909Desktop | 379 | ||
| 10 | Site A | 12/14/22 | Mobile | Site A44909Mobile | 102 | ||
| 11 | Site B | 12/14/22 | Desktop | Site B44909Desktop | 400 | ||
| 12 | Site B | 12/14/22 | Mobile | Site B44909Mobile | 150 | ||
Sheet1 | |||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| D9:D12 | D9 | =A9&B9&C9 |
| E9:E12 | E9 | =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 | |||||||
|---|---|---|---|---|---|---|---|
| A | B | C | D | E | |||
| 1 | Site | Device | Date | OS | Metric | ||
| 2 | Site A | desktop | 12/14/22 | Desktop | 379 | ||
| 3 | Site A | Mobile | 12/14/22 | Android | 80 | ||
| 4 | Site A | Mobile | 12/14/22 | iOS | 22 | ||
| 5 | Site B | desktop | 12/14/22 | Desktop | 400 | ||
| 6 | Site B | Mobile | 12/14/22 | Android | 120 | ||
| 7 | Site B | Mobile | 12/14/22 | iOS | 30 | ||
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?
