Matching index to populate value

Hi @Mel S,

Thanks again for the clear explanation. It really helps to understand what you’re trying to achieve.

From what I can see, there might be a small misalignment in the ranges used for your lookup. It looks like your INDEX range starts from row 2 (I2:K5), while the row MATCH is referencing a range that starts from row 1 (H1:H5). This difference can sometimes cause the matched row position to shift slightly.

You could try adjusting the row MATCH range to H2:H5 so that it lines up more closely with the INDEX range. This may help the formula return the expected result when the intersecting cell is empty.

For reference, here’s a version of the formula you might want to test for Column A:

=IF(
OR($N2="",$M2=""),
"N/A",
IFERROR(
IF(
INDEX($I$2:$K$5,MATCH($N2,$H$2:$H$5,0),MATCH($M2,$I$1:$K$1,0))="",
"NO",
"YES"
),
"N/A"
)
)

And for Column B, if you’d like to return the intersect value only when Column A is "YES", you might try:

=IF($A2="YES",INDEX($I$2:$K$5,MATCH($N2,$H$2:$H$5,0),MATCH($M2,$I$1:$K$1,0)),"")

Of course, this is just a suggestion based on what I’m seeing, but hopefully it points you in the right direction. Please feel free to give it a try and let me know how it goes.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.