September 9, 2022
I have an array of valid US state codes (StCodes) and an array of user supplied state codes in a column in table RT.
The following correctly returns an array of positions of valid codes and #N/A for invalid codes. (valid codes are like AZ, CA, etc)
I was hoping to get an array of the positions of invalid codes by wrapping the previous in another MATCH
but that returns #N/A
I'm looking for a solution and an explanation as to what I am doing wrong.
(Note that in the above I used square brackets to show a column name in the RT references, but I could not find a way for them to display in the post.
If I edit the post, the brackets reappear RT!state!)
July 16, 2010
Welcome to our forum!
It's tricky without an example file. If you have a Microsoft 365 license, you can use this formula:
If that's not working, please upload an example Excel file so we can see your question in context and help you further.
P.S. try formatting your formulas with the square brackets in the 'Preformatted' style to see if they get retained properly.