Matching specific characters in a row using INDEX/MATCH

Hi guys,

I'm have an issue in Excel, which I'm so far not able to solve. I try to return the first cell in a row, containing either a number or a specific character. Let's say I have the following row:s

XXO22O
XXXXXX
22OOXX

I want the function to return the first row containing an "O" or a number, but not the X. The results here should be: first row: value in column 3/second row: #N/A/third row: value in column 1.

I have the following seperate functions:

Formula:

Please Login or Register  to view this content.

to match for an "O"

Formula:

Please Login or Register  to view this content.

to match for any number.

I have combined these in a somewhat ugly formula usign an IF statement to see which of the previous formulas results' occurs earlier, e.g. has a lower column number, and return thr value of that result. I tried to post the formula as well, but got the following error message: Block reason: Your request was not authorized due to its content (HTML code not allowed)

This simply looks at the column number, and gives the value of the cell that has the lowest column number. This would work when I only needed to match for one character and a numeric value However, this is a simplified example, in the real sheet I have to match for a number, or a possible 5 characters. Working this out with the above method would result in a long and complex formula. I am hoping there is a way to vombine the multiple matches I need into one INDEX formula, so that it returns the cell value of the first match that is true.

I'm looking forward to your input, if this is at all possible. Thank you in advance.

Kind regards,

Bart

Hello JeteMc,

Thank you for your reply. Unfortunately, I cannot get the formula to work. The first part returns column number within the array of the first "O", but the second part, matching the TRUE with ISNUMBER returns #N/B in my sheet. I can use it with my formula to match for a number (using the INDEX function) but then it matches the column number returned by the match in your formula, with the value of the cell returned by the second part of the formula. This second part would be 2, thus the result is 2.

Do you know why the match on isnumber returns an error? And as I am now matching a column number, with a cell value, do you know if there is a way to simply return the cell which either has an "O" or a number, and not the column number? I am trying to do an OFFSET on the first cell in the array containing an "O" or a number.

Any help will be highly appreciated

Kind regards,

Bart

Hi JeteMc,

Thank you for your reply! These functions are really helping me in solving my sheet. I got most parts to work now, but unfortunately I run into an error I can't solve yet. I'm sorry to ask for your expertise again, but maybe you know what causes this and if there is a workaround:

If the formula tries to match for a character that is absent in the search array, the functions returns #N/A even if there is a number in the array. The function should return the number (cell of the number in this case), as this match criteria is met. Unfortunately it returns an error. Do you think there is a workaround for this or have I now run into the limits of Excel?

You can see this in your sheet by changing the "O" to another character. The result is now #N/A instead of 2 (the indirect value of cell B4). Any ideas on this?