Matt Pinkston
asked on
Complex excel lookup/formula using multiple tabs
I have a sheet with several tabs that I need to do some lookups and calculations
Main tab is called BreakDown
Key columns are A-Customer, C-Total ARR, E-Product
Then F through M which we will set
Tab called DontDelete is a tab for lookup
Key Columns are A-Product, G-WebPct, H-DBPct, I-APIPct, J-FedPct, K-DefPct, L-StatePct, M-VenPct
Tab Called MRR
Key Columns are A-Customer, C-ARR
Logic for BreakDown Column C#
take MRR(C#) where BreakDown(A#) Matches MRR(A#)
Logic for BreakDown Column F#
BreakDown(C#) * DontDelete(G#) Where BreakDown(E#) = DontDelete(A#)
Main tab is called BreakDown
Key columns are A-Customer, C-Total ARR, E-Product
Then F through M which we will set
Tab called DontDelete is a tab for lookup
Key Columns are A-Product, G-WebPct, H-DBPct, I-APIPct, J-FedPct, K-DefPct, L-StatePct, M-VenPct
Tab Called MRR
Key Columns are A-Customer, C-ARR
Logic for BreakDown Column C#
take MRR(C#) where BreakDown(A#) Matches MRR(A#)
Logic for BreakDown Column F#
BreakDown(C#) * DontDelete(G#) Where BreakDown(E#) = DontDelete(A#)
ASKER
I got the first one to work but this one did not
=C2*VLOOKUP(E2,DontDelete!A2:G149,7,FALSE)
Logic for BreakDown Column F#
BreakDown(C#) * DontDelete(G#) Where BreakDown(E#) = DontDelete(A#)
What does the 7 mean?
=C2*VLOOKUP(E2,DontDelete!A2:G149,7,FALSE)
Logic for BreakDown Column F#
BreakDown(C#) * DontDelete(G#) Where BreakDown(E#) = DontDelete(A#)
What does the 7 mean?
ASKER
7 is the column index to return the value from.
»bp
»bp
ASKER
I shared the actual attachment above you can see it is not working
ASKER
Attaching File
Testxxx.xlsx
Formula provided is not working
Logic for BreakDown Column F#
BreakDown(C#) * DontDelete(G#) Where BreakDown(E#) = DontDelete(A#)
Testxxx.xlsx
Formula provided is not working
Logic for BreakDown Column F#
BreakDown(C#) * DontDelete(G#) Where BreakDown(E#) = DontDelete(A#)
You weren't locking the lookup range in the formula (using $) so when you copied it, Excel adjusted the search range and that was causing a problem. I corrected for that. In practice I typically define the search ranges as defined Names, but I didn't want to throw too much into my first example and dilute the basic lookup parts...
You still do have a number of lookups that fail, that's what the #N/A values are indicating, that's based on the data in the sheet. The #N/A could be replaced with a blank, or some other value in the cell, but for now that is the default and calls attention to the unmatched rows.
Testxxx.xlsx
»bp
You still do have a number of lookups that fail, that's what the #N/A values are indicating, that's based on the data in the sheet. The #N/A could be replaced with a blank, or some other value in the cell, but for now that is the default and calls attention to the unmatched rows.
Testxxx.xlsx
»bp
ASKER
It still does not look right, example
E2 of Break down = GovSearch Plus
C2 = 3625
DontDelete line 13 A13 = GovSearch Plus, G13 = 1 so the result should have been 1 * 3625
E2 of Break down = GovSearch Plus
C2 = 3625
DontDelete line 13 A13 = GovSearch Plus, G13 = 1 so the result should have been 1 * 3625
There is no match for the text "GovSearch Plus" (with a space before Plus) in the "DontDelete" sheet:
| Product |
| GovSearch |
| GovSearch Enterprise (Library) |
| GovSearch Enterprise |
| GovSearch County & Muni |
| GovSearch Defense |
| GovSearch Federal |
| GovSearch Muni |
| GovSearch State |
| GovSearch State Enterprise |
| GovSearch State & Local |
| GovSearch State & Local 50 user Enterprise |
| GovSearchPlus |
| GovSearchSuite |
| GovSearchSuite Enterprise |
| GovSearchSuite Enterprise (Library) |
| FedSearchSuite Enterprise + State & Local |
| State Governments Series |
| FedSearch DB |
| FedSearchSuite DB |
| GovSearch State & Local DB |
| GovSearch Fed-Custom-Defense |
| GovSearchPlusDB |
| GovSearch Muni DB |
| GovSearchSuite DB |
| CountyOnly |
ASKER
When it returns a #N/A how is that changed to 0?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Wohooo!!!!
👍
EE29216846.xlsx
»bp