Display different types of error if accurate results not found on multiple match

[paradise2sr]

Hi,
Currently I am using below formula

{(INDEX(S!$BC$4:$BC$249,MATCH(D!T2&V2&X2,S!$A$4:$A$5000&S!$V$4:$V$5000&S!$AJ$4:$AJ$5000,0))}

What I want is that-

1.To display error of data containing in cell reference : D!$T$1 (If out of 3, 1st match is not found)

2.To display error of data containing in cell reference : D!$V$1 (If out of 3, 2nd match is not found which mean 1st & 3rd match found

3.To display error of data containing in cell reference : D!$X$1 (If out of 3, 3rd match is not found which mean 1st & 2nd match found

4.To display error of data containing in cell reference : D!$V$1&D!$X$1 (If out of 3, 2 &3rd match is not found which mean 1st match is only found)
& likewise.

Currently using above formula I am getting #NA error which I want to elaborate much deeper the cause using above cell reference.
Anyone can alter the formula or provide the best or comprehensive formula taking into account above things.

[protonLeah]

800 posts and no workbook
Your index range is 246 rows 4 -249. Your match range is 4997 rows so what happen if the match is found in row 250, or 2500, ...?

[paradise2sr]

If the match is found it will display the result from column B i.e within range S!$BC$4:$BC$249.All is about bifurcation of #NA error

[AliGW]

This won't be possible without VBA, if at all. Shall I move the thread?

Please don't ignore requests to provide a workbook.

[paradise2sr]

Sure you can move.I will sure provide workbook soon as I have to minimise data from original file.

[paradise2sr]

Ok I have added file and done little changes the references and worksheet name and added few data.I have mentioned sample results which I require in BD column of DO worksheet and highlighted with green.

Let me explain again,matching is done between two worksheet DO column AY with that of worksheet SO column BD .Now if match is not found it will display error.Here only the error part bifurcation I require which I have mentioned in BD column of DO worksheet.

Plz note matching of multiple columns can be done by any formula so this can be altered as per flexibility but the error description is most important which I have mentioned.

[Gregb11]

Well, since no one else has answered yet, I'll give you my solution - it seems to work, but it's not elegant. You can probably make it more elegant, but I don't have the time to put into and, like I said, it seems to work. It involves several helper columns to make it simpler for me (which I've hidden in the attachment for aesthetics, but you can unhide them to see. The helper columns check for each column to match on. Since you are on Excel V2019, you have access to formula TEXTJOIN, which I also employ.

After checking to see if each element matched, I put this formula in BD2 and copied it down:
=IF(BE2="***SaleOrderNo","***Sales Order",BF2&TEXTJOIN(" &",TRUE,BG2:BI2))

Please see the attached.

[paradise2sr]

Thanx for your response.If you could club into a single formula that would be most benefitted to me since I have to deal with large data set with several other formula it.in it.

[Gregb11]

1. I think it would be better as helper columns for ease of modifying later instead of having one huge formula that's hard to decipher.
2. You can put these helper columns in any column you want (way far to the right if you want) so they are out of the way, plus, like I said, you can hide them.
3. I'm not even sure it can be put into one formula, so I can't help there, sorry.

[paradise2sr]

I hope someone would help us in deriving into single one.