Convert array formula to "regular" formula or alternatively, how to force user to use Ctrl+Shift+Enter

[Bering]

Hello,

I built the below array formula for a spreadsheet that we use at the office (column B).

• ={ IFERROR(VLOOKUP(INDEX($E$1:$E$16,MATCH(TRUE,ISNUMBER(FIND($E$1:$E$16,A1,2)),0),1),$E$1:$F$16,2,0),"N/A")}

My concern is that, since the file will be used by many not-so-Excel savvy users, the { } might desappear for whatever reason, resulting in incorrect results.

Are you aware of any reliable way to prevent this potential issue to occur?

If feasible, I think the best way would be to re-work the formula into an equivalent regular (not array) formula. Alternatively, maybe something like:

• Conditional formatting to highlight the missing {} hence reminding user to press Ctrl+Shift+Enter
• VBA to re-add the the missing { }

Of course, I have no idea how to achieve any of those...


Any suggestion will be much appreciated!!

Thanks.

 

[Ron Coderre]

Here you go....This regular formula seems to do what you want:

=IFERROR(VLOOKUP(INDEX($E$1:$E$16,MATCH(TRUE,INDEX(ISNUMBER(FIND($E$1:$E$16,A1,2)),0),0),1),$E$1:$F$16,2,0),"N/A")

Does it?

 

[XOR LX]

The VLOOKUP seems entirely superfluous to me:

=IFERROR(INDEX($F$1:$F$16,MATCH(TRUE,INDEX(ISNUMBER(FIND($E$1:$E$16,A1,2)),0),0)),"N/A")

Regards

 

[Bering]

Thank you sooo much Ron, this works perfectly!! So, an additional Index formula did the trick... good to know

Have great day!

 

[Bering]

Thanks for your comment. I do need the vlookup to retrieve data in column F, once a match is found. Ron's formula works perfectly.

The VLOOKUP seems entirely superfluous to me:

=IFERROR(INDEX($F$1:$F$16,MATCH(TRUE,INDEX(ISNUMBER(FIND($E$1:$E$16,A1,2)),0),0)),"N/A")

Regards

 

[Ron Coderre]

It's a clunky way of getting the value from Col_F. I chose to keep the formula close to what the OP was familiar with.
This is a more concise approach:

=IFERROR(INDEX($E$1:$F$16,MATCH(TRUE,INDEX(ISNUMBER(FIND($E$1:$E$16,A1,2)),0),0),2),"N/A")

And even that can probably be improved upon.

 

[XOR LX]

Thanks for your comment. I do need the vlookup to retrieve data in column F, once a match is found. Ron's formula works perfectly.

My formula is returning from column F. Try it next to yours and Ron's and let me know if you ever find a difference. Personally I can't see how you could, though please prove me wrong.

Regards

 

[Bering]

oops, my apologies, you are right... I misunderstood your comment.

I confirm that all the three solutions proposed give me the correct answer.

You guys rock!! Thanks again ray:

My formula is returning from column F. Try it next to yours and Ron's and let me know if you ever find a difference. Personally I can't see how you could, though please prove me wrong.

Regards