Lookup - Exact match needed

billywiz1307 wrote:

Hi Barry

It's because in the formula description in Numbers, the syntax shows close match on the end and Lookup does not as it happens. See below.

LOOKUP

(search-for, search-where, result-values)

VLOOKUP

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

HLOOKUP

(search-for, rows-range, return-row, close-match)

regards

Dave

something i stumbled across that helped me with VLOOKUP (and other functions) was to use the 'insert function' button via the function browser.. when you do that, it gives you a sort of blueprint of the functions and in the case of VLOOKUP, you'll get a little dropdown for the match portion like so:

billywiz1307 wrote:

Ok, thanks guys, I'm used to Excel and therefore not used to the innovative way you can use and modify formulae in Apple's products. Many thanks, got it now.

I guess that it's one of the reasons why Apple designed well done free resources.

Yvan KOENIG (VALLAURIS, France) jeudi 30 juin 2011 16:59:41

iMac 21”5, i7, 2.8 GHz, 4 Gbytes, 1 Tbytes, mac OS X 10.6.8

Please : Search for questions similar to your own before submitting them to the community

To be the AW6 successor, iWork MUST integrate a TRUE DB, not a list organizer !

The Numbers help describes this argument as follows:

• 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.

  
  

  There I think you should set this argument to 0 or FALSE

Hi Billy,

As you've noted, exact match is not supported in LOOKUP.

What makes you feel VLOOKUP or HLOOKUP (both of which do support exact match) would be unsuitable for the task?

Regards,

Barry