How to maximize the number of tested conditions without using multiple nested IFs?

Rnkhch

Active Member
Joined
Apr 28, 2018
Messages
468
Office Version
  1. 365
Platform
  1. Windows
Hello,

I'm writing a LAMDA function that considers three identifying pieces of information, i.e. subject_ID (column A), specimen_ID (column B), and scan_ID (column C) in my "Data" sheet, to determine drug_level (from column H of "Input" sheet). The Input sheet contains the same three columns A, B, and C as the Data sheet.

Due to various reasons, not all three pieces of information are listed for all samples, and each subject_ID may have more than one sample. So scan_ID and specimen_ID both refer to a unique sample, but subject_ID may refer to multiple scan-IDs and/or specimen_IDs.

Now, ideally I want to check all three (maximum) pieces of information at the beginning of the calculation, in order to get the drug_level. Then if I get an error value, then drop one of the three criteria at a time (i.e. check 2 of the three), and then if that doesn't work, use only one of the three, and if nothing comes up by the end of the function, get a message like "No data found!". And for all steps I want to include a check to ensure the output would be unique (except for subject_ID alone test), and if not I would want to get a message like "double check input data for accuracy!". And for subject_ID alone test, if there are more than one result, I could get a message like "more than one sample found".

I can of course do this myself through multiple nested IFs, but I was thinking there should likely be a more elegant/advanced way of doing this which would be shorter/neater?

If so, I would appreciate any directions!
 
Last edited:

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

johnnyL

Well-known Member
Joined
Nov 7, 2011
Messages
3,977
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
XL2BB Sample data would be nice to see.

Your calculation that you use would be nice to see.

Basically, provide as much info as you can to help you solve the issue. Remember, we can't see what you are looking at.
 

Rnkhch

Active Member
Joined
Apr 28, 2018
Messages
468
Office Version
  1. 365
Platform
  1. Windows
Yeah, sure, just a few minutes until I put together a sample data 🤗
 

Rnkhch

Active Member
Joined
Apr 28, 2018
Messages
468
Office Version
  1. 365
Platform
  1. Windows
Ok, so here is a sample, and in this I put the formula for checking all three conditions.

Book1
ABCDE
1Subject_IDSpecimen_IDScan_IDDrug_Level
2A1B1C1#NAME?
3A1B2C2#NAME?
4A2B3C3#NAME?
5A3B4C4#NAME?
6A4B5C5#NAME?
7A1B6C6#NAME?
8
Data
Cell Formulas
RangeFormula
D2:D7D2=XLOOKUP(1,(AINPUTcA=A2)*(AINPUTcB=B2)*(AINPUTcC=C2),AINPUTcH)


AINPUTcA is a dynamic array that refers to column A of Input and specifies the range from A2 to where there is data. And the same with AINPUTcB and AINPUTcC.

If there was no other way, I would put this in nested IFs like this:

=LET(a,XLOOKUP(1,(AINPUTcA=A2)*(AINPUTcB=B2)*(AINPUTcC=C2),AINPUTcH),IF(ISERROR(a),LET(b,XLOOKUP(1,(AINPUTcA=A2)*(AINPUTcB=B2),AINPUTcH),IF(ISERROR(b),(continue with other permutations),b)),a))

I would eventually put this in a LAMBDA with three parameters:

=LAMBDA(subject_ID,specimen_ID,scan_ID,LET(a,XLOOKUP(1,(AINPUTcA=subject_ID)*(AINPUTcB=specimen_ID)*(AINPUTcC=scan_ID),AINPUTcH),IF(ISERROR(a),LET(b,XLOOKUP(1,(AINPUTcA=subject_ID)*(AINPUTcB=specimen_ID),AINPUTcH),IF(ISERROR(b),(continue with other permutations),b)),a)))
 

Forum statistics

Threads
1,181,695
Messages
5,931,493
Members
436,790
Latest member
aelbaitam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top