Need Assistance With Formula Creation

[meppwc]

I would appreciate assistance with a formula.

This formula will reside in cell C2 of the “Asset List” worksheet.
Search on cell A2, and then on cell B2 of the “Asset List”
Search in worksheet “Equipment” in column G
If only A2 is found, then, “MSN FOUND”
If only B2 is found, then, “CSN FOUND”
If A2 and B2 are both found, then, “BOTH FOUND”
If nothing found, then, “NOTHING FOUND”

 

[Jeffrey Mahoney]

Please try this:
=LET(a,COUNTIF(G:G,A2),b,COUNTIF(G:G,B2),IFS(AND(a>0,b>0),"BOTH FOUND",a>0,"MSN FOUND",b>0,"CSN FOUND",a+b=0,"NOTHING FOUND"))

 

[Peter_SSs]

Another to try

=LET(G,Equipment!G:G,CHOOSE(IFNA((MATCH(A2,G,0)>0),0)+2*IFNA((MATCH(B2,G,0)>0),0)+1,"NOTHING","MSN","CSN","BOTH")&" FOUND")

 

[meppwc]

Jeffrey. Thank you. I did notice that I am getting incorrect results when one of the values being searched on is a blank cell. I can get around it by changing G:G to G2:G10238, which excluded the blank cells that have data in them. But I was wondering if there is a simpler way around that so that I don't have to keep changing the column length as my data contineously changes.

 

[Peter_SSs]

Jeffrey. Thank you.

Did you try the other suggestion?

 

[meppwc]

Another to try

=LET(G,Equipment!G:G,CHOOSE(IFNA((MATCH(A2,G,0)>0),0)+2*IFNA((MATCH(B2,G,0)>0),0)+1,"NOTHING","MSN","CSN","BOTH")&" FOUND")[/COThank
[/QUOTE]

Did you try the other suggestion?

That did not work. All cells provide the same result."NOTHING"

 

[Jeffrey Mahoney]

Jeffrey. Thank you. I did notice that I am getting incorrect results when one of the values being searched on is a blank cell. I can get around it by changing G:G to G2:G10238, which excluded the blank cells that have data in them. But I was wondering if there is a simpler way around that so that I don't have to keep changing the column length as my data contineously changes.

Yeah, You can create a Dynamic named range. I use them for everything. If the Data in column starts in row 2 then the formula for the range would be:
=Offset($G$1,1,0,match("zzzzzzzzzzzzz",$G:$G,1)-row($G$1),1)
This will adjust itself based on the values in the column. NAme it want you want and change the other formula accordingly

 

[meppwc]

Yeah, You can create a Dynamic named range. I use them for everything. If the Data in column starts in row 2 then the formula for the range would be:
=Offset($G$1,1,0,match("zzzzzzzzzzzzz",$G:$G,1)-row($G$1),1)
This will adjust itself based on the values in the column. NAme it want you want and change the other formula accordingly

Great, thanks............I have never done that before so I will work with it soon, when time permits

 

[Peter_SSs]

That did not work. All cells provide the same result."NOTHING"

In that case it sounds like either the description was mis-interpreted or the data is not quite as it seems it should be.
Here is my sample data and results. Is this what you would expect? Certainly the results are not all "Nothing"
Perhaps you could provide some small sample data from the two sheets with XL2BB as I have done and then we can test with your data.

meppwc.xlsm
	A	B	C	D	E	F	G
1
2							a
3							b
4							c
5							d
6
Equipment

meppwc.xlsm
	A	B	C
1
2	z	w	NOTHING FOUND
3	a	q	MSN FOUND
4	z	b	CSN FOUND
5	a	b	BOTH FOUND
6			NOTHING FOUND
7	a		MSN FOUND
8		b	CSN FOUND
9	z		NOTHING FOUND
10		w	NOTHING FOUND
Asset List

Cell Formulas
Range	Formula
C2:C10	C2	=LET(G,Equipment!G:G,CHOOSE(IFNA((MATCH(A2,G,0)>0),0)+2*IFNA((MATCH(B2,G,0)>0),0)+1,"NOTHING","MSN","CSN","BOTH")&" FOUND")