Forum Discussion
Multiple IF formula calucation
Hi All,
I have a 2 datasets similar to the below.
Data Set 1
| Pressure | Plume 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
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.
12 Replies
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.
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.
- You may use INDEX MATCH function with multiple criteria.
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.
