Formula for Incorporating Numerical Maximum and Alphabetical Qualifier In One Cell

[oshmunnies]

I am seeking a formula (or combined formulas) that will produce two types of results, depending on the type of data present in a given row.

Please see the attached example spreadsheet in conjunction with the following explanation:

Possibility 1: If all values in the 'Result' columns of a given row are "<", produce a result of "ND" in column A.

Possibility 2: If at least one value in the 'Result' columns of a given row is a numerical value, select the maximum (if only one numerical value exists, that would be the maximum), AND incorporate "J" (if it is present in the 'Qualifier' column) in column A.

I hope that makes sense. I am hoping to find a formula (or combined formulas) that yields results for both possibilities (i.e. that can simply be copied down for all rows of column A), because I have many thousands of rows of data. Any ideas?

Much thanks in advance.

[Phuocam]

Try this ...

=IF(COUNT(B4:I4)=0,"ND",MAX(B4:I4)&INDEX(C4:I4,MATCH(MAX(B4:H4),B4:H4,0)))

[oshmunnies]

This is perfect! Wow, thank you for such a speedy response! You've been a great help

[oshmunnies]

Unfortunately, however, my example was too simplistic for some of my data.

I have made a couple of adjustments to the example table that more accurately represent the format of the data. The formula you provided works great under some conditions but not under others. I modified the formula a tad, which solved some of the issues, but not all. See new attachment. Thanks again for any help.

[Phuocam]

Edit in cell A7:

=IF(COUNT(B7:Q7)=0,"ND",MAX(B7:Q7)&INDEX(B7:Q7,CEILING(MATCH(MAX(B7:Q7),B7:Q7,0),4)))