Question about Numbers vLookup

Question

Question about Numbers vLookup

I am new to Numbers, but pretty skilled in Excel. This is also my first time posting a question to this forum so please let me know if you'd like me to provide more detail.

I am trying to find an Excel vLookup equivalent in Numbers that'll accomplish the following:

Find an exact match of a string of characters from Column A in a different tab called "Vlookup" and return the string of characters from the corresponding Column B from that same "Vlookup" tab. My Excel formula was this: VLOOKUP(C133, Vlookup!$A$2:$B$267,2, FALSE).

- Vlookup / Hlookup in Numbers seem to require a numeric value return - I need to return a string.

- Lookup in Numbers almost works but doesn't seem to have the option to specify an exact match - I need to find an exact match. It's incorrectly pulling the closest match instead of returning error when a match is not found.

Thanks!

MacBook Air (13-inch, Early 2015), OS X Yosemite 10.10.4

Posted on Aug 30, 2015 8:54 AM

Reply

Answer 1

Best reply

t quinn

Level 5

7,042 points

Aug 30, 2015 11:01 AM in response to mrsnelson2012

Hi mrsnelson,

Where V/HLOOKUP() can run into difficulty is if you want to return a value from a column to the left of your search term. I have found that the combination of INDEX and MATCH is wonderfully flexible.

quinn

Reply

Answer 2

Barry

Level 9

59,861 points

Aug 31, 2015 12:03 AM in response to mrsnelson2012

Hi Mrs Nelson,

Here are the details for VLOOKUP in Numbers.

The VLOOKUP function returns a value from a range of columns by using the left column of values to pick a row and a column number to pick a column in that row.

VLOOKUP(search-for, columns-range, return-column, close-match)

search-for: The value to find. search-value can contain any value type.
columns-range: A range of cells. range is a reference to a single range of cells, which may contain values of any type.
return-column: A number that specifies the relative column number of the cell from which to return the value. return-column is a number value. The leftmost column in the range is column 1.
close-match: An optional value that determines whether an exact match is required.
- close match (TRUE, 1, or omitted): If there’s no exact match, select the column with the largest top-row value that is less than the search value. Wildcards can’t be used in search-for.
- exact match (FALSE or 0): If there’s no exact match, return an error. Wildcards can be used in search-for.

As you can see, the syntax is pretty much the same as the MS Excel version. The only difference I see is in addressing the range on the lookup table. I am assuming that by "in a different tab called Vlookup" you mean 'in a different table named Vlookup in the same document.'

Your formula:

=VLOOKUP(C133, Vlookup!$A$2:$B$267,2, FALSE)

Numbers version

=VLOOKUP(C133, Vlookup::$A$2:$B$267,2, FALSE)

Here is the copied and pasted version of the formula used in the example:

=VLOOKUP(A2, Vlookup :: $A$2:$B$10,2, FALSE)

Note that the returned value can be text or number (or date and time or duration), and that an error message is returned if 'exact match' is specified and an exact match is not found.

Regards,

Barry

Reply