MacMost Q&A Forum • View All Forum QuestionsAsk a Question

How To Edit LookUp Equations In Numbers?


I am after a bit of help to write a formula as the new update for numbers has my existing formula now coming up with errors.

I was using the look up function to find e.g.: look up ( apple a1, in table 1 a1:a30, to return a $ f1:30) this was bringing back a correct $ and if there was no $ it would come up with ! Or if apple wasn’t in the list it would come up with !. This was fine!

IT isn’t doing that now if apple isnt in the list it is returning $2 or returning a non existent value.

I Have tried vlookup it returns apple instead of $, I have tried combinations of formulas together.

So I was hoping some one would be able to help me write a formula that looks a bit like this

lookup a1, intabel1 a1:a100, return table1 f1:100 (if exact match a1 and in table 1 a1:a100) returns $ in table f1:f100 if not it returns not found

Is this possible?


Comments: One Response to “How To Edit LookUp Equations In Numbers?”

    7 years ago

    I don't think anything changed for the LOOKUP function nor the VLOOKUP function in the latest version of Numbers. But perhaps you are referring to a much older version originally?

    Anyway, the LOOKUP function will return the last value if the search value is not found. It won't give an error.

    The VLOOKUP is what you want. It works differently, so read the help entry on it carefully. You'd want to specify the value, then the full range of cells, so a1:f30. Then the offset for the answer, so row f is 6 off from row a, so 6.

    Then you still get the last value if the value is not present. But if you look at the help entry you see there is a 4th parameter that you can specify as FALSE so it returns an error instead of a value.


    Hope that helps.

Comments Closed.