sum of two drop down lists

[feistyb]

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

Thanks in advance!

[#2]

@feistyb 

=XLOOKUP(pom,column_pom,column_p)+XLOOKUP(htm,column_htm,column_h)

[#3]

@feistyb 

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), "")

[#4]

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:  

=IFERROR(XLOOKUP(B13,BL[BL DESCRIPTION],BL[BL'#])+XLOOKUP(C13,BLHTM[HTM DESCRIPTION],BLHTM[HTM'#]),)

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. 

[#5]

@feistyb 

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?

[#6]

@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!

[#7]

@feistyb 

Does this do what you want?

[#8]

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

[#9]

@feistyb 

Which version of Excel do you have?

[#10]

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

[#11]

@feistyb 

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

[#12]

I think its Microsoft 365.

[#13]

@feistyb 

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.

[#14]

OK, Thank you for your help.

[#15]

Hi Hans,
I have found out that I have Excel for Mac 2019. Does this change anything?

[#16 · Accepted solution]

@feistyb 

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

Try this formula instead:

=IF(B13="",0,INDEX(BL[BL'#],MATCH(B13,BL[BL DESCRIPTION],0)))+IF(C13="",0,INDEX(BLHTM[HTM'#],MATCH(C13,BLHTM[HTM DESCRIPTION],0)))

Workbook with this formula attached.

[#17]

OMG! IT WORKED!!!!

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