Jun 11 2021 04:38 AM
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
Jun 11 2021 04:47 AM
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.
Jun 11 2021 04:51 AM
Jun 11 2021 04:56 AM
@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.
Jun 11 2021 05:27 AM
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
Jun 11 2021 08:57 AM
@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.
Jun 11 2021 10:17 AM
As a comment, to be safe I'd use separator for concatenation to avoid situation when =1&23 and =12&3 return the same.
Jun 14 2021 12:08 AM
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
Jun 14 2021 12:29 AM
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.
Jun 14 2021 12:45 AM
Jun 15 2021 03:45 AM
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,
Jun 15 2021 04:13 AM
@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(...........),"").
Jun 15 2021 04:33 AM
Jun 14 2021 12:29 AM
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.