# 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#)
Microsoft ExcelMicrosoft Office

Last Comment
Bill Prew

8/22/2022 - Mon
Bill Prew

This should do what you described, and give you the idea...

EE29216846.xlsx

»bp
Matt Pinkston

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?
Matt Pinkston

Bill Prew

7 is the column index to return the value from.

»bp
Matt Pinkston

I shared the actual attachment above you can see it is not working
Matt Pinkston

Attaching File
Testxxx.xlsx

Formula provided is not working
Logic for BreakDown Column F#
BreakDown(C#) * DontDelete(G#) Where BreakDown(E#) = DontDelete(A#)
Bill Prew

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
Matt Pinkston

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
Bill Prew

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

Matt Pinkston

When it returns a #N/A how is that changed to 0?