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

[Rnkhch]

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!

 

[johnnyL]

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]

Yeah, sure, just a few minutes until I put together a sample data

 

[Rnkhch]

Ok, so here is a sample, and in this I put the formula for checking all three conditions.

Book1
	A	B	C	D	E
1	Subject_ID	Specimen_ID	Scan_ID	Drug_Level
2	A1	B1	C1	#NAME?
3	A1	B2	C2	#NAME?
4	A2	B3	C3	#NAME?
5	A3	B4	C4	#NAME?
6	A4	B5	C5	#NAME?
7	A1	B6	C6	#NAME?
8
Data

Cell Formulas
Range	Formula
D2:D7	D2	=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)))