VLOOKUP formula

It makes the formula a bit longer, but it is doable.

The revised formula uses a pair of nested IF statements. Here's the logic:

IF1 is true (nothing in A2), ""(null string), otherwise,

IF2 is true (company is listed), company name fro column 3, otherwise,

(Person's name from column 2)

IF(LEN(A2)<1,"",

IF(LEN(VLOOKUP(A2,Contacts::A:C,3,FALSE))>1,VLOOKUP(A2,Contacts::A:C,3,FALSE),

VLOOKUP(A2,Contacts::A:C,2,FALSE)))

Pastable copy:

IF(LEN(A2)<1,"",IF(LEN(VLOOKUP(A2,Contacts::A:C,3,FALSE))>1,VLOOKUP(A2,Contacts: :A:C,3,FALSE),VLOOKUP(A2,Contacts::A:C,2,FALSE)))

Regards,

Barry

Hi ryszardb,

Your question is not clear.

The formula shown is delivering what it is written to do: get a number from 'this row'of column A (of 'this table'), search for that number in the first column of the lookup table—columns A to C of "Contacts", and return the value on the same row of the second column of the lookup table.

The empty cell in row 4 of "Contacts" is read as the numerical value zero.

Your label on column B of "Data" indicates you want both the name and the company name returned to cells in that column. To do that, you will need two VLOOKUP statements in the formula, with their results concatenated into a single value.

The error triangles mark 'couldn't find' errors. To avoid them, wrap the formula in an IF statement that prevents the VLOOKUPs being called if there is no entry in column A.

The single space separator is added regardless of one of the other cells being empty in the Contacts table.

Note the space in front of "Company 3". There's also a space after "Name 2" in the following row.

Enclosing the formula in TRIM() will remove those spaces.

Full formula:

TRIM(IF(LEN(A2)<1,"",VLOOKUP(A2,Contacts::A:C,2,FALSE)&" "&VLOOKUP(A2,Contacts::A:C,3,FALSE)))

Core formula in bold, IF 'switch' in normal type, TRIM part in italics.

Regards,

Barry

@SG,

Cleaner, which I like. The extra column on Contacts could be hidden.

In B2, filled down:

=VLOOKUP(A2,Contacts::A:D,4,FALSE)

Does this need a 'cut off switch' to avoid a 'can't find' error in rows 7 and 8?

IF(LEN(A2)<1,"",VLOOKUP(A2,Contacts::A:D,4,FALSE))

Regards,

Barry

You can try this:

In D2, filled down:

=IF(LEN(C2)>1,C2,B2)

In B2, filled down:

=VLOOKUP(A2,Contacts::A:D,4,FALSE)

SG

I understand your question is how to make the answer to be blank, not 0.

If it is a formula such as =VLOOKUP(A,Contacts::A:B,2)

make this simple change to it =VLOOKUP(A,Contacts::A:B,2)&""

And to get rid of error triangles it would be =IFERROR(VLOOKUP(A,Contacts::A:B,2)&"","")

EDIT: and don't forget the "exact match" argument in VLOOKUP like I did in my examples.