LinkBack URL
About LinkBacks
Hello everyone,
I am using Microsoft Excel 2016.
If a range contains one of the values in the array, I wonder how can I return the index of the array?
Please see the attachment for the samples.
Thanks a lot,
Lawrence
Hello everyone,
I am using Microsoft Excel 2016.
If a range contains one of the values in the array, I wonder how can I return the index of the array?
Please see the attachment for the samples.
Thanks a lot,
Lawrence
Last edited by LawCarrot; 03-01-2018 at 04:05 AM.
Try:
=LOOKUP(1,-SEARCH($E$2:$E$5,A2),$F$2:$F$5)
In B2
=IF(SUMPRODUCT((--ISNUMBER(SEARCH($E$1:$E$5,A2))*ROW($E$1:$E$5))),INDEX($F$1:$F$5,SUMPRODUCT((--ISNUMBER(SEARCH($E$1:$E$5,A2))*ROW($E$1:$E$5)))),"N/A")
copy down
Thank you very much for helping out too, JohnTopley!
Your solution works perfectly on my sample workbook, but once I adjusted and applied it to another workbook, it keeps on showing the #REF! error
I guess the problem may be that my array and index list is not on the same worksheet of the range's worksheet, that's why the error is caused
Last edited by LawCarrot; 03-01-2018 at 04:11 AM.
Thank you very much, Phuocam! It works exactly as expected! Do you mind explaining a bit on how it works?
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks