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

Question

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

sergeibaklan · Answer

Amanda535&nbsp;
You are welcome.
If continue&nbsp;mathetes&nbsp;idea, you may name range with Mileage Matrix let say as&nbsp;MileageMatrix, when formula could be
&nbsp;
=IFNA(INDEX( MileageMatrix,
       MATCH([@[From ]],INDEX(MileageMatrix,0,1),0),
       MATCH([@To],INDEX(MileageMatrix,1,0),0)
),0)

mathetes · Answer

Amanda535&nbsp;&nbsp;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.&nbsp;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

sergeibaklan · Answer

Amanda535&nbsp;
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

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

amanda535 · Answer

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

sergeibaklan · Answer

Amanda535&nbsp;
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)
&nbsp;

Forum Discussion

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

7 Replies

Resources