SOLVED

Multiple IF formula calucation

%3CLINGO-SUB%20id%3D%22lingo-sub-2439040%22%20slang%3D%22en-US%22%3EMultiple%20IF%20formula%20calucation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2439040%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20All%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%202%20datasets%20similar%20to%20the%20below.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EData%20Set%201%26nbsp%3B%3C%2FP%3E%3CTABLE%20border%3D%221%22%20width%3D%22136.04972375690608%25%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2250%25%22%20height%3D%2230px%22%3EPressure%3C%2FTD%3E%3CTD%20width%3D%2225%25%22%20height%3D%2230px%22%3EPlume%20Size%3C%2FTD%3E%3CTD%20width%3D%2221.2707182320442%25%22%3E%3CP%3E%3CSTRONG%3EFlowrate%20(kg%2Fhr)%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2221.2707182320442%25%22%3E%3CP%3E%3CSTRONG%3EFlowrate%20(T%2Fhr)%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2218.50828729281768%25%22%3E%3CP%3E%3CSTRONG%3ESave%20Value%20(k%24%2Fyr)%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2250%25%22%20height%3D%2230px%22%3E%3CP%3E3%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2225%25%22%20height%3D%2230px%22%3E%3CP%3E0.5%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2221.2707182320442%25%22%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2221.2707182320442%25%22%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2218.50828729281768%25%22%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2250%25%22%20height%3D%2230px%22%3E%3CP%3E3%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2225%25%22%20height%3D%2230px%22%3E%3CP%3E1%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2221.2707182320442%25%22%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2221.2707182320442%25%22%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2218.50828729281768%25%22%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2250%25%22%20height%3D%2230px%22%3E%3CP%3E3%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2225%25%22%20height%3D%2230px%22%3E%3CP%3E2%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2221.2707182320442%25%22%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2221.2707182320442%25%22%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2218.50828729281768%25%22%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2250%25%22%20height%3D%2230px%22%3E%3CP%3E3%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2225%25%22%20height%3D%2230px%22%3E%3CP%3E3%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2221.2707182320442%25%22%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2221.2707182320442%25%22%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2218.50828729281768%25%22%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2250%25%22%20height%3D%2230px%22%3E%3CP%3E3%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2225%25%22%20height%3D%2230px%22%3E%3CP%3E4%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2221.2707182320442%25%22%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2221.2707182320442%25%22%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2218.50828729281768%25%22%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EData%20Set%202%26nbsp%3B%3C%2FP%3E%3CTABLE%20border%3D%221%22%20width%3D%2299.86187845303867%25%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2220.718232044198896%25%22%3E%3CP%3E%3CSTRONG%3EPressure%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2218.093922651933703%25%22%3E%3CP%3E%3CSTRONG%3EPlume%20Size%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2221.2707182320442%25%22%3E%3CP%3E%3CSTRONG%3EFlowrate%20(kg%2Fhr)%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2221.2707182320442%25%22%3E%3CP%3E%3CSTRONG%3EFlowrate%20(T%2Fhr)%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2218.50828729281768%25%22%3E%3CP%3E%3CSTRONG%3ESave%20Value%20(k%24%2Fyr)%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2220.718232044198896%25%22%3E%3CP%3E3%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2218.093922651933703%25%22%3E%3CP%3E0.5%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2221.2707182320442%25%22%3E%3CP%3E5.2647%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2221.2707182320442%25%22%3E%3CP%3E0.00526%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2218.50828729281768%25%22%3E%3CP%3E0.59954%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2220.718232044198896%25%22%3E%3CP%3E3%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2218.093922651933703%25%22%3E%3CP%3E1%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2221.2707182320442%25%22%3E%3CP%3E7.89706%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2221.2707182320442%25%22%3E%3CP%3E0.0079%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2218.50828729281768%25%22%3E%3CP%3E0.89932%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2220.718232044198896%25%22%3E%3CP%3E3%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2218.093922651933703%25%22%3E%3CP%3E2%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2221.2707182320442%25%22%3E%3CP%3E33.612%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2221.2707182320442%25%22%3E%3CP%3E0.03361%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2218.50828729281768%25%22%3E%3CP%3E3.82773%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2220.718232044198896%25%22%3E%3CP%3E3%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2218.093922651933703%25%22%3E%3CP%3E3%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2221.2707182320442%25%22%3E%3CP%3E93.2505%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2221.2707182320442%25%22%3E%3CP%3E0.09325%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2218.50828729281768%25%22%3E%3CP%3E10.6194%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2220.718232044198896%25%22%3E%3CP%3E3%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2218.093922651933703%25%22%3E%3CP%3E4%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2221.2707182320442%25%22%3E%3CP%3E147.056%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2221.2707182320442%25%22%3E%3CP%3E0.14706%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2218.50828729281768%25%22%3E%3CP%3E16.7467%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2220.718232044198896%25%22%3E%3CP%3E3%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2218.093922651933703%25%22%3E%3CP%3E5%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2221.2707182320442%25%22%3E%3CP%3E215.417%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2221.2707182320442%25%22%3E%3CP%3E0.21542%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2218.50828729281768%25%22%3E%3CP%3E24.5317%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20will%20be%20entering%20data%20into%20dataset%201%20(Pressure%20%26amp%3B%20Plume%20Size).%20I%20want%20to%20use%20dataset%202%20as%20a%20look%20up%20table%20so%20if%20the%20pressure%20and%20plume%20size%20match%20a%20line%20in%20dataset%202%20the%20other%20columns%20need%20to%20auto%20populate%20according%20to%20the%20match.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20suggestions%20would%20be%20much%20appreciated.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMany%20thanks%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EElliot%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2439040%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-2439074%22%20slang%3D%22en-US%22%3ERe%3A%20Multiple%20IF%20formula%20calucation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2439074%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1036873%22%20target%3D%22_blank%22%3E%40Serdet%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThat%20could%20be%20like%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DXLOOKUP(Pressure%20%26amp%3B%20%22%3D%22%20%26amp%3B%20PlumeSize%2C%0A%20DataSet2Pressure%20%26amp%3B%20%22%3D%22%20%26amp%3B%20DataSet2PlumeSize%2C%0A%20DataSet2FlowRate%2C%22not%20found%22)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Ein%20Data%20Set%201%20FlowRate%20column%20cell%2C%20drag%20it%20to%20the%20down.%20Similar%20for%20other%20ones.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2439076%22%20slang%3D%22en-US%22%3ERe%3A%20Multiple%20IF%20formula%20calucation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2439076%22%20slang%3D%22en-US%22%3EYou%20may%20use%20INDEX%20MATCH%20function%20with%20multiple%20criteria.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2439095%22%20slang%3D%22en-US%22%3ERe%3A%20Multiple%20IF%20formula%20calucation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2439095%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1036873%22%20target%3D%22_blank%22%3E%40Serdet%3C%2FA%3E%26nbsp%3BIn%20case%20the%20suggested%20solution%20with%20XLOOKUP%20is%20not%20supported%20by%20your%20Excel%20version%2C%20the%20use%20of%20INDEX%20and%20MATCH%20is%20demonstrated%20in%20the%20attached%20file.%20I%20used%20some%20named%20ranges%20to%20make%20the%20formulae%20easier%20to%20write%2C%20read%20and%20maintain.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2439996%22%20slang%3D%22en-US%22%3ERe%3A%20Multiple%20IF%20formula%20calucation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2439996%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1036873%22%20target%3D%22_blank%22%3E%40Serdet%3C%2FA%3E%26nbsp%3BCutting%20a%20few%20corners%20here%2C%20because%20it%20can%20be%20done%20better.%20just%20don't%20have%20the%20time%20right%20now%2C%20but%20the%20attached%20file%20contains%20working%20formulae.%20Note%20that%20I%20also%20added%20the%20named%20ranges%20required.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Hi All,

 

I have a 2 datasets similar to the below.

 

Data Set 1 

PressurePlume Size

Flowrate (kg/hr)

Flowrate (T/hr)

Save Value (k$/yr)

3

0.5

 

 

 

3

1

 

 

 

3

2

 

 

 

3

3

 

 

 

3

4

 

 

 

 

Data Set 2 

Pressure

Plume Size

Flowrate (kg/hr)

Flowrate (T/hr)

Save Value (k$/yr)

3

0.5

5.2647

0.00526

0.59954

3

1

7.89706

0.0079

0.89932

3

2

33.612

0.03361

3.82773

3

3

93.2505

0.09325

10.6194

3

4

147.056

0.14706

16.7467

3

5

215.417

0.21542

24.5317

 

I will be entering data into dataset 1 (Pressure & Plume Size). I want to use dataset 2 as a look up table so if the pressure and plume size match a line in dataset 2 the other columns need to auto populate according to the match.

 

Any suggestions would be much appreciated.

 

Many thanks,

 

Elliot  

12 Replies

@Serdet 

That could be like

=XLOOKUP(Pressure & "=" & PlumeSize,
 DataSet2Pressure & "=" & DataSet2PlumeSize,
 DataSet2FlowRate,"not found")

in Data Set 1 FlowRate column cell, drag it to the down. Similar for other ones.

You may use INDEX MATCH function with multiple criteria.

@Serdet In case the suggested solution with XLOOKUP is not supported by your Excel version, the use of INDEX and MATCH is demonstrated in the attached file. I used some named ranges to make the formulae easier to write, read and maintain.

Hi @Riny_van_Eekelen 

 

Thanks for getting back to me. I have tried to implement the formular used in your spreadsheet but are having problems due to my 'dataset2' being located on a different 'Tonnage' tab.

 

Please see the attached.

 

Many thanks,

 

Elliot 

@Serdet Cutting a few corners here, because it can be done better. just don't have the time right now, but the attached file contains working formulae. Note that I also added the named ranges required.

 

 

@Riny_van_Eekelen 

As a comment, to be safe I'd use separator for concatenation to avoid situation when =1&23 and =12&3 return the same.

Hi @Riny_van_Eekelen,

 

Thank you for sending that over. I have copied your formula into my spreadsheet but are receiving a #NAME? error.

 

I have double-checked the two but cannot spot any differences. Any ideas on where I might be going wrong?

 

Many thanks,

 

Elliot 

best response confirmed by Serdet (Occasional Contributor)
Solution

@Serdet As indicated in my previous post, I used some named ranges (dataset2, headers, pressure and plume size). This makes the formula easier to write, read and maintain. Added them to your sheet. See attached.

 

Thank you very much for your help! Have a good day

Hi @Riny_van_Eekelen ,

 

Sorry to bother you again. I have implemented the formula into the attached excel spreadsheet and are revieving errors.

 

I have tried renaming the cell selections but have had no luck. Any tips?

 

Many thanks,

@Serdet I assume you refer to the errors in columns P, Q and R. Fixed the formulae in these columns and copied the headers in P2:R2 to C1:E1 in the Tonnage sheet. These headers must be exactly the same, otherwise the MATCH will not work.

 

If you want to get rid of the remaning #N/A's (due to the fact that there are no values for Pressure and Plume size), wrap the entire formula in =IFERROR(INDEX(...........),"").

Thank you very much. I was assuming the C$2 referred to the column located in the separate 'Tonnage' tab.