VLOOKUP

You may find the more modern XLOOKUP easier to use. In fact, the syntax is the same as what you tried! But if you want you can also specify what to display if the function doesn't find a match, as in this example:

=XLOOKUP(B$1,Client List::A,Client List::B,"not found")

Now that we have XLOOKUP there is no advantage to using VLOOKUP or (the truly ancient) LOOKUP.

More on XLOOKUP here. If you're using Numbers on the Mac you can find the same information (on XLOOKUP and other functions) by going to Help > Formulas and Functions Help in your menu.

SG

Are you sure it is XLOOKUP (B1,Client List::A, Client List::$B:$C,"not found", Wildcard, First to last) ? That formula does not seem to make sense and should give an error.

• Client List::$B:$C should be Client List:$B . The :$C part does nothing. You can include it but it is useless unless you use INDEX to access it.
• Wildcard (2) should be invalid and result in an error. You want it to be "exact" (0) or leave it off.

When corrected it will look exactly like the formula SGIII provided.

You are misunderstanding how vlookup works.

adjust you formula like this:

=VLOOKUP(A2,Client List::A:E, 2)

this will return the second column, the name.

IF you want the address use:

=VLOOKUP(A2,Client List::A:E, 3)

IF you want the phone use:

=VLOOKUP(A2,Client List::A:E, 4)

The error arises from your using the syntax of LOOKUP, which is different from the syntax for VLOOKUP.

The error message tells you what the error is.

Argument 3 of VLOOKUP in your example is "Client List::B"

The syntax for VLOOKUP is:

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

Search for: (the value in)B2,

columns-range: Client List::A:B (columns A and B of the Client list table 

VLOOKUP always searches the leftmost column of the specified columns range.

return-column: 2

You want VLOOKUP to return the value from the second column of the columns range.

in the row in which it found the search value.

close-match

This tells VLOOKUP to accept a 'close-match if there is no exact match (TRUE) or to require an exact match (FALSE)

For the current search (customer #101 a 'close match' would be the largest number less than or equal to 101. If 101 was not on the list, and 98, 99 and 100 were on the list, a close-match would be 100, and exact-match would return a not forun error.

Edited formula for cell B2 of the invoice: VLOOKUP(B1,Client List::A:B,2,exact match)

Regards,

Barry

All formulas I posted, to match your specific situation, should use B1 rather than A2.

e.g.

=VLOOKUP(A2,Client List::A:E, 2)

should be:

=VLOOKUP(B1,Client List::A:E, 2)