Need help with combining formulas

[sspatriots]

Hi,

I have these two XLOOKUP formulas that I need to combine into one. Both of them will work independently of one another, but I need the multiple criteria to apply in one formula.

First formula:

=XLOOKUP("*"&[@[G1
Job '#]]&"*",tblTable4711[Job '#],tblTable4711[PO '#],,2)

Second formula:

=XLOOKUP("*" & "Machine" & "*", tblTable4711[Job List Equipment],tblTable4711[PO '#],,2)

I tried applying the Boolean logic of the formula (sample below) to apply both conditions, but all I get back is the "#N/A" error.

=XLOOKUP(1,(criteria1)*(criteria2)*(criteria3),data)

The formula I tried using to combine the first two conditions into a single formula is shown below:

=XLOOKUP(1, (tblTable4711[Job '#] = "*"&[@[G1
Job '#]]&"*") * (tblTable4711[Job List Equipment] = "*" & Machine & "*"),tblTable4711[PO '#],,2)

Note that I am using a wildcard match in both formulas. I'm hoping someone can see what I maybe doing wrong here so I can get it fixed. Thank, SS

 

[sspatriots]

I think I have it sorted. I found where someone used "IFERROR" with the "SEARCH" function in the XLOOKUP formula to apply multiple criteria conditions and it appears to be working for me so far. Below is what I'm testing out right now:

=XLOOKUP(1, IFERROR(SEARCH("*"&[@[G1
Job '#]]&"*", tblTable4711[Job '#])^0 * SEARCH( "*" & 'Machine & "*", tblTable4711[Job List Equipment])^0, 0), tblTable4711[PO '#], "", 0, -1)

 

[Peter_SSs]

Firstly, when posting formulas in the forum, please use the Formulas tags, not the Quote tags.

Secondly, I have removed the solution mark from post #2 as what is posted is not a valid formula. Incorrect quote marks I believe.

Thirdly, in the post #2 formula (when corrected) there is no need for both the wildcard characters (*) and the ^0 calculation. One or the other is sufficient.
I think that the formula you meant to post in post #2 was

=XLOOKUP(1, IFERROR(SEARCH("*"&[@[G1 Job '#]]&"*", tblTable4711[Job '#])^0 * SEARCH( "*" & "Machine" & "*", tblTable4711[Job List Equipment])^0, 0), tblTable4711[PO '#], "", 0, -1)

Without the ^0 calculation it would be

=XLOOKUP(1, IFERROR(SEARCH("*"&[@[G1 Job '#]]&"*", tblTable4711[Job '#]) * SEARCH( "*" & "Machine" & "*", tblTable4711[Job List Equipment]), 0), tblTable4711[PO '#], "", 0, -1)

or without the wildcards it would be

=XLOOKUP(1, IFERROR(SEARCH([@[G1 Job '#]], tblTable4711[Job '#])^0 * SEARCH("Machine", tblTable4711[Job List Equipment])^0, 0), tblTable4711[PO '#], "", 0, -1)

As a matter of interest this also works, at least on my test data (you will get more help if you provide sample data in the forum)

=LET(f,FILTER(tblTable4711[PO '#],IFERROR(SEARCH([@[G1 Job '#]],tblTable4711[Job '#])*SEARCH("Machine",tblTable4711[Job List Equipment]),0),""),INDEX(f,ROWS(f)))

All 4 of the above formula produce the same results for me.

 

[Alex Blakenburg]

@sspatriots The way you have posted you formulas indicates to me that you have a carriage return in your heading G1 Job #, assuming that is the case this is an XLookup version along the lines of your Boolean logic thoughts.

20240309 XLookup reference table heading with cr sspatriots.xlsx
	A	B	C
1
2	Job #	Job List Equipment	PO #
3	123456	This is machine xx	abc
4	987654	another machine y	def
5
6
7
8
9	G1 Job #	Xlookup
10	345	abc
11	876	def
Sheet1

Cell Formulas
Range	Formula
B10:B11	B10	=XLOOKUP(1,ISNUMBER(SEARCH([@[G1 Job '#]],tblTable4711[[#All],[Job '#]]))*ISNUMBER(SEARCH("machine",tblTable4711[[#All],[Job List Equipment]])),tblTable4711[[#All],[PO '#]])

 

[sspatriots]

Firstly, when posting formulas in the forum, please use the Formulas tags, not the Quote tags.
View attachment 108112

Secondly, I have removed the solution mark from post #2 as what is posted is not a valid formula. Incorrect quote marks I believe.

Thirdly, in the post #2 formula (when corrected) there is no need for both the wildcard characters (*) and the ^0 calculation. One or the other is sufficient.
I think that the formula you meant to post in post #2 was

=XLOOKUP(1, IFERROR(SEARCH("*"&[@[G1 Job '#]]&"*", tblTable4711[Job '#])^0 * SEARCH( "*" & "Machine" & "*", tblTable4711[Job List Equipment])^0, 0), tblTable4711[PO '#], "", 0, -1)

Without the ^0 calculation it would be

=XLOOKUP(1, IFERROR(SEARCH("*"&[@[G1 Job '#]]&"*", tblTable4711[Job '#]) * SEARCH( "*" & "Machine" & "*", tblTable4711[Job List Equipment]), 0), tblTable4711[PO '#], "", 0, -1)

or without the wildcards it would be

=XLOOKUP(1, IFERROR(SEARCH([@[G1 Job '#]], tblTable4711[Job '#])^0 * SEARCH("Machine", tblTable4711[Job List Equipment])^0, 0), tblTable4711[PO '#], "", 0, -1)

As a matter of interest this also works, at least on my test data (you will get more help if you provide sample data in the forum)

=LET(f,FILTER(tblTable4711[PO '#],IFERROR(SEARCH([@[G1 Job '#]],tblTable4711[Job '#])*SEARCH("Machine",tblTable4711[Job List Equipment]),0),""),INDEX(f,ROWS(f)))

All 4 of the above formula produce the same results for me.

Sorry, didn't realize there was a button posting a formula, so I had used the quotes. Noted. Not sure what you are referring too about the solution in post #2 not be valid. I copied what worked from my worksheet straight to the post. Maybe the use of the Quote tags is why you are saying it is invalid. I can repost it on Monday using that other formula tag you mentioned. Thanks for the suggestions. I'll plug them in on Monday to see if they make it faster. I like the shorter version without the wildcards, I'll try this formula first.

 

[sspatriots]

@sspatriots The way you have posted you formulas indicates to me that you have a carriage return in your heading G1 Job #, assuming that is the case this is an XLookup version along the lines of your Boolean logic thoughts.

20240309 XLookup reference table heading with cr sspatriots.xlsx
	A	B	C
1
2	Job #	Job List Equipment	PO #
3	123456	This is machine xx	abc
4	987654	another machine y	def
5
6
7
8
9	G1 Job #	Xlookup
10	345	abc
11	876	def
Sheet1

Cell Formulas
Range	Formula
B10:B11	B10	=XLOOKUP(1,ISNUMBER(SEARCH([@[G1 Job '#]],tblTable4711[[#All],[Job '#]]))*ISNUMBER(SEARCH("machine",tblTable4711[[#All],[Job List Equipment]])),tblTable4711[[#All],[PO '#]])

I do have a carriage return in the headers. It was that way when I inherited the file and don't want to rock the boat since there are so many users. Your formula looks just like what I was trying to do when first started trying to combine them. I will give this a closer look on Monday when I return to work. Thank you, SS

 

[Peter_SSs]

Not sure what you are referring too about the solution in post #2 not be valid.

Under the left red line there is a single quote when it should be a double quote.
Under the right red line there is nothing when there should be a double quote.

Maybe the use of the Quote tags is why you are saying it is invalid.

It is not related to using Quote tags.

I do have a carriage return in the headers.

I had missed that (some XL2BB sample data would help with that sort of thing) so the formulas I posted before would become.

The formula I think you meant to post

=XLOOKUP(1, IFERROR(SEARCH("*"&[@[G1
Job '#]]&"*", tblTable4711[Job '#])^0 * SEARCH( "*" & "Machine" & "*", tblTable4711[Job List Equipment])^0, 0), tblTable4711[PO '#], "", 0, -1)

Without ^0

=XLOOKUP(1, IFERROR(SEARCH("*"&[@[G1
Job '#]]&"*", tblTable4711[Job '#]) * SEARCH( "*" & "Machine" & "*", tblTable4711[Job List Equipment]), 0), tblTable4711[PO '#], "", 0, -1)

Without wildcards

=XLOOKUP(1, IFERROR(SEARCH([@[G1
Job '#]], tblTable4711[Job '#])^0 * SEARCH("Machine", tblTable4711[Job List Equipment])^0, 0), tblTable4711[PO '#], "", 0, -1)

Alternative

=LET(f,FILTER(tblTable4711[PO '#],IFERROR(SEARCH([@[G1
Job '#]],tblTable4711[Job '#])*SEARCH("Machine",tblTable4711[Job List Equipment]),0),""),INDEX(f,ROWS(f)))

@Alex Blakenburg
Alex, I think that your suggestion omits two features of the OP's (not quite right) formula. Yours returns a) a #N/A error rather than "" if no rows meet the criteria and b) data from the first matching row rather than the last matching row if multiple rows meet the criteria. Perhaps these additions at the end.

=XLOOKUP(1,ISNUMBER(SEARCH([@[G1
Job '#]],tblTable4711[[#All],[Job '#]]))*ISNUMBER(SEARCH("machine",tblTable4711[[#All],[Job List Equipment]])),tblTable4711[[#All],[PO '#]],"",,-1)

 

[sspatriots]

View attachment 108130
Under the left red line there is a single quote when it should be a double quote.
Under the right red line there is nothing when there should be a double quote.



It is not related to using Quote tags.



I had missed that (some XL2BB sample data would help with that sort of thing) so the formulas I posted before would become.

The formula I think you meant to post

=XLOOKUP(1, IFERROR(SEARCH("*"&[@[G1
Job '#]]&"*", tblTable4711[Job '#])^0 * SEARCH( "*" & "Machine" & "*", tblTable4711[Job List Equipment])^0, 0), tblTable4711[PO '#], "", 0, -1)

Without ^0

=XLOOKUP(1, IFERROR(SEARCH("*"&[@[G1
Job '#]]&"*", tblTable4711[Job '#]) * SEARCH( "*" & "Machine" & "*", tblTable4711[Job List Equipment]), 0), tblTable4711[PO '#], "", 0, -1)

Without wildcards

=XLOOKUP(1, IFERROR(SEARCH([@[G1
Job '#]], tblTable4711[Job '#])^0 * SEARCH("Machine", tblTable4711[Job List Equipment])^0, 0), tblTable4711[PO '#], "", 0, -1)

Alternative

=LET(f,FILTER(tblTable4711[PO '#],IFERROR(SEARCH([@[G1
Job '#]],tblTable4711[Job '#])*SEARCH("Machine",tblTable4711[Job List Equipment]),0),""),INDEX(f,ROWS(f)))

@Alex Blakenburg
Alex, I think that your suggestion omits two features of the OP's (not quite right) formula. Yours returns a) a #N/A error rather than "" if no rows meet the criteria and b) data from the first matching row rather than the last matching row if multiple rows meet the criteria. Perhaps these additions at the end.

=XLOOKUP(1,ISNUMBER(SEARCH([@[G1
Job '#]],tblTable4711[[#All],[Job '#]]))*ISNUMBER(SEARCH("machine",tblTable4711[[#All],[Job List Equipment]])),tblTable4711[[#All],[PO '#]],"",,-1)

Thanks. I appreciate the explanation.

 

[sspatriots]

Firstly, when posting formulas in the forum, please use the Formulas tags, not the Quote tags.
View attachment 108112

Secondly, I have removed the solution mark from post #2 as what is posted is not a valid formula. Incorrect quote marks I believe.

Thirdly, in the post #2 formula (when corrected) there is no need for both the wildcard characters (*) and the ^0 calculation. One or the other is sufficient.
I think that the formula you meant to post in post #2 was

=XLOOKUP(1, IFERROR(SEARCH("*"&[@[G1 Job '#]]&"*", tblTable4711[Job '#])^0 * SEARCH( "*" & "Machine" & "*", tblTable4711[Job List Equipment])^0, 0), tblTable4711[PO '#], "", 0, -1)

Without the ^0 calculation it would be

=XLOOKUP(1, IFERROR(SEARCH("*"&[@[G1 Job '#]]&"*", tblTable4711[Job '#]) * SEARCH( "*" & "Machine" & "*", tblTable4711[Job List Equipment]), 0), tblTable4711[PO '#], "", 0, -1)

or without the wildcards it would be

=XLOOKUP(1, IFERROR(SEARCH([@[G1 Job '#]], tblTable4711[Job '#])^0 * SEARCH("Machine", tblTable4711[Job List Equipment])^0, 0), tblTable4711[PO '#], "", 0, -1)

As a matter of interest this also works, at least on my test data (you will get more help if you provide sample data in the forum)

=LET(f,FILTER(tblTable4711[PO '#],IFERROR(SEARCH([@[G1 Job '#]],tblTable4711[Job '#])*SEARCH("Machine",tblTable4711[Job List Equipment]),0),""),INDEX(f,ROWS(f)))

All 4 of the above formula produce the same results for me.

I went back and removed the part that was "^0" and that seemed to be the path of least resistance. Also, I see what you are saying about the quotes in the original formula. Not sure how that happened, but looks like I left the double quotes off around the word "Machine" and had a single tick mark instead. Thanks again for yours and everyone else's help, Regards, SS