Home

VLOOKUP Not Doing What Is Expected

%3CLINGO-SUB%20id%3D%22lingo-sub-493803%22%20slang%3D%22en-US%22%3EVLOOKUP%20Not%20Doing%20What%20Is%20Expected%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-493803%22%20slang%3D%22en-US%22%3E%3CP%3EI'm%20a%20music%20publisher%2C%20and%20twice%20a%20year%2C%20I%20have%20to%20calculate%20how%20much%20royalties%20I%20need%20to%20pay%20to%20my%20writers%20%26amp%3B%20co-publishers.%26nbsp%3B%20This%20is%20a%20complex%20%26amp%3B%20tedious%20task%2C%20and%20to%20aid%20in%20that%20task%2C%20I%20have%20a%20big%20honkin'%20spreadsheet%20to%20calculate%20the%20royalties%20%26amp%3B%20to%20generate%20reports%20for%20the%20recipients.%26nbsp%3B%20The%20problem%20today%20is%20that%20the%20formulas%20that%20worked%206%20months%20ago%20now%20don't%20work.%26nbsp%3B%20Here%20the%20deal%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20get%20a%20huge%20database%20spreadsheet%20with%20tens%20of%20thousands%20of%20records%2C%20with%20each%20record%20representing%20a%20single%20instance%20of%20royalty%20income.%26nbsp%3B%20My%20problem%20is%20that%20this%20huge%20DB%20list%20an%20%22income%20type%22%20that%20is%20different%20from%20the%20%22income%20type%22%20used%20by%20me%20%26amp%3B%20my%20recipients%2C%20so%20I%20use%20some%20additions%20to%20translate%20the%20original%20%22income%20type%22%20to%20my%20%22income%20type%22.%26nbsp%3B%20Take%20a%20look%20at%20this...%26nbsp%3B%20(Note%2C%20the%20attached%20file%20will%20be%20easier%20to%20view).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%3E%3CTBODY%3E%3CTR%3E%3CTD%3EIncome%20Type%20Description%3C%2FTD%3E%3CTD%3EAmount%20Earned%3C%2FTD%3E%3CTD%3EMyType%3C%2FTD%3E%3CTD%3EMech%3C%2FTD%3E%3CTD%3EPartial%20Mech.%3C%2FTD%3E%3CTD%3ESync%3C%2FTD%3E%3CTD%3EFull%2FPart%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ESYNC%20-%20STREAMING%20VIDEO%3C%2FTD%3E%3CTD%3E0.01%3C%2FTD%3E%3CTD%3ESync%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3CTD%3E0.01%3C%2FTD%3E%3CTD%3EFull%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ESYNC%20-%20STREAMING%20VIDEO%3C%2FTD%3E%3CTD%3E0.01%3C%2FTD%3E%3CTD%3ESync%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3CTD%3E0.01%3C%2FTD%3E%3CTD%3EFull%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ESYNC%20-%20STREAMING%20VIDEO%3C%2FTD%3E%3CTD%3E0.01%3C%2FTD%3E%3CTD%3ESync%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3CTD%3E0.01%3C%2FTD%3E%3CTD%3EFull%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ESTREAMING%20-%20Mechanical%3C%2FTD%3E%3CTD%3E0.02%3C%2FTD%3E%3CTD%3EPart%20Mech%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3CTD%3E0.02%3C%2FTD%3E%3CTD%3E0.02%3C%2FTD%3E%3CTD%3EPart%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ESTREAMING%20-%20Mechanical%3C%2FTD%3E%3CTD%3E0.03%3C%2FTD%3E%3CTD%3EPart%20Mech%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3CTD%3E0.03%3C%2FTD%3E%3CTD%3E0.03%3C%2FTD%3E%3CTD%3EPart%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ESTREAMING%20-%20Mechanical%3C%2FTD%3E%3CTD%3E0.03%3C%2FTD%3E%3CTD%3EPart%20Mech%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3CTD%3E0.03%3C%2FTD%3E%3CTD%3E0.03%3C%2FTD%3E%3CTD%3EPart%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ESTREAMING%20-%20Mechanical%3C%2FTD%3E%3CTD%3E0.03%3C%2FTD%3E%3CTD%3EMech%3C%2FTD%3E%3CTD%3E0.03%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3CTD%3E0.03%3C%2FTD%3E%3CTD%3EFull%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ESTREAMING%20-%20Mechanical%3C%2FTD%3E%3CTD%3E0.03%3C%2FTD%3E%3CTD%3EMech%3C%2FTD%3E%3CTD%3E0.03%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3CTD%3E0.03%3C%2FTD%3E%3CTD%3EFull%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ESTREAMING%20-%20Mechanical%3C%2FTD%3E%3CTD%3E0.03%3C%2FTD%3E%3CTD%3EMech%3C%2FTD%3E%3CTD%3E0.03%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3CTD%3E0.03%3C%2FTD%3E%3CTD%3EFull%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20%22Income%20Type%20Description%22%20is%20from%20the%20original%20DB%2C%20but%20I%20simplify%20it%20to%20%22MyType%22.%26nbsp%3B%20For%20this%20example%2C%20I%20move%20the%20%22Amount%20Earned%22%20into%20the%20appropriate%20column%20(in%20this%20example%2C%20%22Mech%22%2C%20%22Partial%20Mech%22%2C%20or%20%22Sync%22.%26nbsp%3B%20The%20formulae%20in%20the%20%22Mech%22%20and%20%22Partial%20Mech%22%20columns%20work%2C%20but%20the%20formulae%20in%20the%20%22Sync%22%20ALWAYS%20copies%20the%20%22Amount%20Earned%22%20regardless%20of%20it%20is%20appropriate.%26nbsp%3B%20For%20example%2C%20look%20at%20the%20last%20row%20--%20the%20%22STREAMING%20Mechanical%22%20gets%20looked%20up%20in%20a%20table%20(not%20shown%20here)%20and%20is%20found%20in%20the%20column%20evaluated%20in%20the%20VLOOKUP%20formulae%20in%20the%20%22MyMech%22%20column%2C%20but%20the%20VLOOKUP%20in%20the%20%22Sync%22%20column%20returns%20the%20last%20value%20in%20the%20%22MySync%22%20lookup%20table%20list.%26nbsp%3B%20In%20short%2C%20the%20VLOOKUP%20in%20the%20Sync%20column%20always%20finds%20a%20value%2C%20even%20though%20it's%20not%20in%20the%20list.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%2C%20why%20does%20it%20work%20for%20%22Mech%22%20and%20%22Partial%20Mech%22%20and%20not%20for%20%22Sync%22%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20in%20advance.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-493803%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-494592%22%20slang%3D%22en-US%22%3ERe%3A%20VLOOKUP%20Not%20Doing%20What%20Is%20Expected%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-494592%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F146587%22%20target%3D%22_blank%22%3E%40Jon%20Cohen%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYour%20VLOOKUP()%20does%20not%20make%20sense.%3C%2FP%3E%3CP%3EHere%20is%20my%20suggestion%20in%20the%20attached%20file.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Jon Cohen
Contributor

I'm a music publisher, and twice a year, I have to calculate how much royalties I need to pay to my writers & co-publishers.  This is a complex & tedious task, and to aid in that task, I have a big honkin' spreadsheet to calculate the royalties & to generate reports for the recipients.  The problem today is that the formulas that worked 6 months ago now don't work.  Here the deal:

 

I get a huge database spreadsheet with tens of thousands of records, with each record representing a single instance of royalty income.  My problem is that this huge DB list an "income type" that is different from the "income type" used by me & my recipients, so I use some additions to translate the original "income type" to my "income type".  Take a look at this...  (Note, the attached file will be easier to view).

 

Income Type DescriptionAmount EarnedMyTypeMechPartial Mech.SyncFull/Part
SYNC - STREAMING VIDEO0.01Sync000.01Full
SYNC - STREAMING VIDEO0.01Sync000.01Full
SYNC - STREAMING VIDEO0.01Sync000.01Full
STREAMING - Mechanical0.02Part Mech00.020.02Part
STREAMING - Mechanical0.03Part Mech00.030.03Part
STREAMING - Mechanical0.03Part Mech00.030.03Part
STREAMING - Mechanical0.03Mech0.0300.03Full
STREAMING - Mechanical0.03Mech0.0300.03Full
STREAMING - Mechanical0.03Mech0.0300.03Full

 

The "Income Type Description" is from the original DB, but I simplify it to "MyType".  For this example, I move the "Amount Earned" into the appropriate column (in this example, "Mech", "Partial Mech", or "Sync".  The formulae in the "Mech" and "Partial Mech" columns work, but the formulae in the "Sync" ALWAYS copies the "Amount Earned" regardless of it is appropriate.  For example, look at the last row -- the "STREAMING Mechanical" gets looked up in a table (not shown here) and is found in the column evaluated in the VLOOKUP formulae in the "MyMech" column, but the VLOOKUP in the "Sync" column returns the last value in the "MySync" lookup table list.  In short, the VLOOKUP in the Sync column always finds a value, even though it's not in the list.

 

So, why does it work for "Mech" and "Partial Mech" and not for "Sync"?

 

Thanks in advance.

 

 

 

1 Reply

@Jon Cohen 

Your VLOOKUP() does not make sense.

Here is my suggestion in the attached file.

 

Related Conversations
Extentions Synchronization
Deleted in Discussions on
3 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
35 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
9 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies