Morning,
I am trying to figure out this value error while using a match function but I have not yet had any luck. Any insight in what I am overlooking?
Column C should either give a row number or "N/A"
Morning,
I am trying to figure out this value error while using a match function but I have not yet had any luck. Any insight in what I am overlooking?
Column C should either give a row number or "N/A"
C1 contains a section instruction,not a question. You will never get a match with this:
=MATCH($A2&C$1,$B:$B,FALSE)
Explain in WORDS what you expect this formula to return.
EDIT
Try this:
=MATCH($A2&"*",$B:$B,FALSE)
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Thanks for your quick response Ali. Unfortunately I would need to use the entire question/instructions since zoom polls lay out that way.....and I need to match the answers to the exact question layout (See helper column B). What is unusual is I tried this same format in other question/instructions and it worked. Is there any way to achieve this while using C$1 with your suggestion of "*"?
Ah, sorry - I see now. Well, I don't know how to deal with a lookup like this containing CHAR(10) (a line feed character). Someone else may have an idea.
Match is limited to 255 characters which is why it's not working, try Xmatch insteadFormula:Please Login or Register to view this content.
Ooh!!!
Thanks, Fluff - I hadn't clocked XMATCH's added benefit!
Nor had I. I was going to try using filter & thought about trying xmatch first.
It worked! Thank you
You're welcome & thanks for the feedback.
If you are using INDEX as an array formula along with MATCH in order to be able to retrieve a value, you will need to convert your formula into an array formula, otherwise, you will see a #VALUE! error. Solution: INDEX and MATCH should be used as an array formula, which means you need to press CTRL+SHIFT+ENTER.
Hope This Works,
Peter
The OP is using 365 & therefore does not need to use Ctrl Shift Enter.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks