XLOOKUP - Stopping when one condition is met

[Ozzy23]

Hi All,


I posted yesterday in relation to a XLOOKLUP query I had. Thank you that got resolved with the forums help Another day today and I have another complex problem so any support would be appreciated as I'm learning too.

1. I have a number of ID values (Will call these X) on worksheet 1. These X values correspond to another set of IDs (Will call these Y) on worksheet 2 that have status associated with them FAIL, BLOCKED, etc..
2. The X values can appear more than once so there are different Y IDs and status that correspond with each X value
3. I need the condition to stop once a certain status is met, so for IDX-1 if the status is failed at all then the remaining status does not matter no matter how many times the value appears.

For example:

ID X VALUE (WORKSHEET 1)	CORRESPONDING Y VALUE (WORKSHEET 2)	CORRESPONDING STATUS (WORKSHEET 2)
IDX-1	IDY-11	FAIL
IDX-1	IDY-33	PASS
IDX-1	IDY-44	BLOCKED
IDX-2	IDY-100	PASS

My unsuccessful method

1. I was first trying to do a XLOOKUP to find the corresponding values of Y since this is on another worksheet and return the status. Since the values of X can be found in 4 columns in the lookup array on worksheet 2 I was concatenating using the & for each column.
2. I was then trying to use IF function to try and stop the the return results once it had found the first fail

=XLOOKUP(A2,'Worksheet2'!F$2:F$2515&'Worksheet2'!G$2:G$2515&'Worksheet2'!H$2:H$2515&'Worksheet2'!I$2:I$2515,'Worksheet2'!R$2:R$2515)

 

[sg2209]

Try

Here's the corrected formula:

=IFERROR(INDEX('Worksheet2'!$R$2:$R$2515, MATCH(A2, 'Worksheet2'!$F$2:$F$2515 & 'Worksheet2'!$G$2:$G$2515 & 'Worksheet2'!$H$2:$H$2515 & 'Worksheet2'!$I$2:$I$2515, 0)), "")

Explanation:

• The MATCH function is used to find the first occurrence of the ID X in the concatenated range of ID Y values on Worksheet 2.
• The CONCATENATE operator (&) is used to combine the values from columns F, G, H, and I on Worksheet 2 into a single string.
• The INDEX function retrieves the corresponding status from column R on Worksheet 2.
• The IFERROR function is used to handle cases where there is no match, returning an empty string if there is an error.

Make sure to adjust the ranges ('Worksheet2'!$F$2:$F$2515, 'Worksheet2'!$G$2:$G$2515, 'Worksheet2'!$H$2:$H$2515, 'Worksheet2'!$I$2:$I$2515, 'Worksheet2'!$R$2:$R$2515) to match the actual range of data on Worksheet 2.