Need to calculate based on values from two separate drop down Lists

%3CLINGO-SUB%20id%3D%22lingo-sub-1512590%22%20slang%3D%22en-US%22%3ENeed%20to%20calculate%20based%20on%20values%20from%20two%20separate%20drop%20down%20Lists%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1512590%22%20slang%3D%22en-US%22%3E%3CP%3Elist%20of%2016%20cars%2C%2016%20brands%20and%2016%20different%20prices.%20I%20have%20a%20main%20sheet%20with%20my%20data%2C%20a%20second%20sheet%20with%20the%20table%20containing%20the%20drop%20down%20lists.%20My%20formula%20spills.%20I%20cannot%20for%20the%20life%20of%20me%20figure%20out%20what%20I'm%20missing.%20I've%20tried%20so%20many%3C%2FP%3E%3CP%3E%3DVLOOKUP('Main%20Sheet'!A4%3AA20%2C%5B%40%5BFrom%20%5D%5D%26amp%3B'Main%20Sheet'!A4%3AQ4%2C%5B%40To%5D%2C'Main%20Sheet'!B5%3AB20)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ethis%20is%20my%20latest.%20Anyone%20recognize%20what%20I'm%20missing%3F%20I%20know%20once%20I%20see%20it%2C%20I'll%20have%20that%20AHA%20moment%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1512590%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1513078%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20to%20calculate%20based%20on%20values%20from%20two%20separate%20drop%20down%20Lists%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1513078%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F723959%22%20target%3D%22_blank%22%3E%40Amanda535%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWithout%20seeing%20your%20actual%20workbook%2C%20it's%20hard%20to%20be%20sure.%20I%20do%20notice%20that%20the%20first%20argument%20in%20your%20VLOOKUP%20formula%20is%20itself%20an%20array%2C%20and%20my%20own%20suspicion%20would%20be%20that%20that%20might%20be%20the%20problem.%20Usually%20one%20uses%20a%20single%20cell%20as%20the%20first%20argument.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20were%20to%20post%20your%20actual%20sheet%2C%20we%20(I%20and%20others)%20could%20take%20a%20look%20at%20it.%20Just%20make%20sure%20it's%20not%20got%20any%20confidential%20info%20on%20it%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1513158%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20to%20calculate%20based%20on%20values%20from%20two%20separate%20drop%20down%20Lists%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1513158%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F723959%22%20target%3D%22_blank%22%3E%40Amanda535%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ELookup%20value%20here%20is%20an%20array%2C%20formula%20returns%20an%20array%20for%20which%20is%20not%20enough%20space%20in%20one%20cell%20of%20the%20table%2C%20thus%20%23SPILL%20error%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20786px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F204495i7CA71CE441D5DBB3%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EWhat%20other%20parameters%20mean%20is%20also%20absolutely%20unclear%20(second%20one%20is%20value%20instead%20of%20range%2C%20etc)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1533396%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20to%20calculate%20based%20on%20values%20from%20two%20separate%20drop%20down%20Lists%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1533396%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F425987%22%20target%3D%22_blank%22%3E%40mathetes%3C%2FA%3E%26nbsp%3Bjust%20getting%20laptop%20back%20thanks%20so%20much%20for%20response.%20I've%20attached%20sheet%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1533647%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20to%20calculate%20based%20on%20values%20from%20two%20separate%20drop%20down%20Lists%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1533647%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F723959%22%20target%3D%22_blank%22%20rel%3D%22noopener%22%3E%40Amanda535%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3Ehas%20given%20you%20the%20formula%20that%20handles%20your%20initial%20request.%20I've%20tweaked%20the%20spreadsheet%20just%20a%20bit%20more%20and%20suggest%20you%20not%20%22hard-code%22%20the%20mileage%20rate%20into%20the%20formula%2C%20as%20you%20had%20it.%20Instead%2C%20create%20a%20named%20range%20where%20that%20rate%20is%20called%20%22MileageRate%22%20and%20then%20your%20formulas%20will%20just%20refer%20to%20that%20term.%20That%20way%2C%20next%20year%2C%20when%20the%20rate%20gets%20changed%20(or%20after%20you've%20exceeded%20the%205%2C000%20Km%20limit%2C%20the%20rate%20can%20be%20changed%20at%20the%20level%20of%20that%20little%20table%20rather%20than%20editing%20each%20formula.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1533444%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20to%20calculate%20based%20on%20values%20from%20two%20separate%20drop%20down%20Lists%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1533444%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F723959%22%20target%3D%22_blank%22%3E%40Amanda535%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThat%20could%20be%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DIFNA(INDEX('Main%20Sheet'!%24A%244%3A%24Q%2420%2C%0A%20%20%20%20%20%20%20%20%20%20%20MATCH(%5B%40%5BFrom%20%5D%5D%2C%20%20'Main%20Sheet'!%24A%244%3A%24A%2420%2C0)%2C%0A%20%20%20%20%20%20%20%20%20%20%20MATCH(%5B%40To%5D%2C'Main%20Sheet'!%24A%244%3A%24Q%244%2C0))%2C0)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

list of 16 cars, 16 brands and 16 different prices. I have a main sheet with my data, a second sheet with the table containing the drop down lists. My formula spills. I cannot for the life of me figure out what I'm missing. I've tried so many

=VLOOKUP('Main Sheet'!A4:A20,[@[From ]]&'Main Sheet'!A4:Q4,[@To],'Main Sheet'!B5:B20)

 

this is my latest. Anyone recognize what I'm missing? I know once I see it, I'll have that AHA moment

7 Replies
Highlighted

@Amanda535 

 

Without seeing your actual workbook, it's hard to be sure. I do notice that the first argument in your VLOOKUP formula is itself an array, and my own suspicion would be that that might be the problem. Usually one uses a single cell as the first argument.

 

If you were to post your actual sheet, we (I and others) could take a look at it. Just make sure it's not got any confidential info on it

Highlighted

@Amanda535 

Lookup value here is an array, formula returns an array for which is not enough space in one cell of the table, thus #SPILL error

image.png

What other parameters mean is also absolutely unclear (second one is value instead of range, etc)

Highlighted

@mathetes just getting laptop back thanks so much for response. I've attached sheet

 

Highlighted

@Amanda535 

That could be

=IFNA(INDEX('Main Sheet'!$A$4:$Q$20,
           MATCH([@[From ]],  'Main Sheet'!$A$4:$A$20,0),
           MATCH([@To],'Main Sheet'!$A$4:$Q$4,0)),0)

 

Highlighted

@Amanda535 

 

@Sergei Baklanhas given you the formula that handles your initial request. I've tweaked the spreadsheet just a bit more and suggest you not "hard-code" the mileage rate into the formula, as you had it. Instead, create a named range where that rate is called "MileageRate" and then your formulas will just refer to that term. That way, next year, when the rate gets changed (or after you've exceeded the 5,000 Km limit, the rate can be changed at the level of that little table rather than editing each formula.

Highlighted
You guys are the absolute best! I didnt even try the match thanks so much for you help. Now I'll never forget it
Highlighted

@Amanda535 

You are welcome.

If continue @mathetes idea, you may name range with Mileage Matrix let say as MileageMatrix, when formula could be

 

=IFNA(INDEX( MileageMatrix,
       MATCH([@[From ]],INDEX(MileageMatrix,0,1),0),
       MATCH([@To],INDEX(MileageMatrix,1,0),0)
),0)