sum of two drop down lists

Occasional Contributor

Hi,  I know this questions has probably been asked and answered, but I think I need a more direct answer.  


Thanks in advance!


16 Replies




Let's say that your lookup tables are in A10:D20.


In A2:

=IFERROR(XLOOKUP(B2, B10:B20, A10:A20)+XLOOKUP(C2, C2:C10, D2:D10), "")

Hi Hans, 


Thank you for responding.  I have further questions and will be repeating previous info for clarity.  (apologies for redundant information.   I am not good with this.)


There is a number populating the the desired cell, but the sum is not correct.  This is what I did:







in cell A2 (which reads 0)-  the formula I entered is:  



Cell B2 (where it says FRONT BODY LENGTH) is a drop down list linked to BL description column in ORANGE CHART

Cell B3 (where it says HPS to Skirt Overlap) is a drop down list linked to HTM description column in BLUE CHART.  


You advised : =IFERROR(XLOOKUP(B2, B10:B20, A10:A20)+XLOOKUP(C2, C2:C10, D2:D10), "")

I followed the layout, but my data for the charts/drop down lists are located in another sheet.  And omitted "".  When "" are added, nothing populates. 


These are my data charts:

ORANGE is BL chart

BLUE is HTM chart

















My desired result is to have cell A2 read 101.04 when the formula is correct.  



Thank you for your time. 



Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?

@Hans Vogelaar 


Yes, please see attached.  Tabs/sheets are highlighted in green and red.  

The data charts are in red "data set(DO NOT USE PRINT)" tab

The cells that I want to populate using formula are in green "GRADED SPEC" tab. 


If there is anything else I can make clearer for you, pls let me know. 


Again, thank you for your time!


Does this do what you want?

Unfortunately, no.
I am not able to select other options in the drop down lists and have them sum correctly/without error.


Which version of Excel do you have?

I think I'm using Version 16.66.1? That's what it says in "about excel"


And the Office version? Microsoft 365, or Office 2021, or Office 2019, or ...?

I think its Microsoft 365.


In that case, I don't understand why it doesn't work for you, sorry. The result of the formula changes when I select different items from the drop downs in columns B and C.

OK, Thank you for your help.
Hi Hans,
I have found out that I have Excel for Mac 2019. Does this change anything?
best response confirmed by feistyb (Occasional Contributor)


Yes, that makes all the difference! XLOOKUP is only available in Microsoft 365 and Office 2021, not in Office 2019.

Try this formula instead:


Workbook with this formula attached.


Thank you so so much Hans. Really. Thank you for your patience and diligence!