VLOOKUP change?

I'm not aware of any changes (although it's possible), but I am seeing something similar....

First, I can reproduce your problem (at least what I think is the problem weaned from your screenshots), and can generate an error. I assumed that the problem was the way that VLOOKUP() was concatenating the A3 & " " & C3 values, so I wrapped them in an explicit CONCAT():

=VLOOKUP(CONCAT(A3&" "&C3),Plants::A:D,4,exact match)

and it worked (even through the 'correct' form for CONCAT() would be CONCAT(A3," ",C3), but I digress.

So that got me looking at the way Numbers is combining the strings, and that's when it got weirder...

I then removed the CONCAT() statement, essentially reverting to the original form of the equation, and it continued to work.

So, something is definitely amiss with the way VLOOKUP() is combining the string elements into the lookup value. It may be worth reporting as a bug (via Numbers -> Provide Numbers Feedback), and in the meantime, wrapping the search element inside a CONCAT() seems to address the issue.

Hi newleaflandscaping,

I don't know what is going on, but just a thought: any spurious spaces in either table would cause a mismatch.

Anyway, I thought I would try the newer XLOOKUP instead of the older VLOOKUP.

A much reduced "Plants" table with one Header Row:

Formula in "Plant Material" D3 is XLOOKUP(A3&" "&C3,Plants::A,Plants::D,"Not Found",0)

More on XLOOKUP here:

XLOOKUP – Apple Support (AU)

Regards,

Ian.

Thank you so much for your help! I tried it in several different places but wasn't able to get it to work yet but I'll keep trying a few different things.

Thank you so much Ian, looks like that formula did the trick!