vlookup with concatenate

  • Thread starter Thread starter Chris
  • Start date Start date
C

Chris

I am looking to reference a worksheet where I need two
columns concatenated (one cell from one column with one
cell from another column) before returning the value to
the detination sheet.

Example: the reference sheet- (names in column A),
B1= 2, C1=N

So, when doing my vlookup I would like to return N2 to
the destination sheet. And if the either field is blank
just return the value from one of them (either the N or 2
in this case), or if they are both blank - just return
blank. Can you help?

Please give a little explanation when writing out the
formula so I may begin to learn the logic behind the
formulas. I understand vlookups but nesting and
stipulations don't make sense to me when using formulas.

Thank you very much in advance.
 
Chris,

Just concatenate the 2 lookups, like
=VLOOKUP(A1,$F$1:$H$8,3,FALSE)&VLOOKUP(A1,$F$1:$H$8,2,FALSE)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Assume the reference table is in Sheet 2, range A1:C4, as below:

ABC 2 N
DEF 3
GHJ P
KLM

Name the range A1:C4 as: RefTable

In Sheet 1,
you have names in col A, A1 downwards you want to vlookup the names in col A
and retrieve the required info into col B.

put in B1:
=IF(ISNA(TRIM(VLOOKUP(TRIM(A1),RefTable,2,FALSE)&VLOOKUP(TRIM(A1),RefTable,3
,FALSE))),"",TRIM(VLOOKUP(TRIM(A1),RefTable,2,FALSE)&VLOOKUP(TRIM(A1),RefTab
le,3,FALSE)))

copy down col B

Sample output:

XYZ
ABC 2N
DEF 3
KLM
GHJ P

--------------

VLOOKUP(...)
4th parameter is set to FALSE for exact match

VLOOKUP(...)&VLOOKUP(...)
is used to concatenate the returns from the 2nd & 3rd cols in RefTable into
one

TRIM(A1)
is used instead of plain "A1" in the VLOOKUP(...) to ensure that there will
be a correct match in the event of any inadvertent leading or trailing
spaces in the names (text) in col A.

TRIM(VLOOKUP(...))
is used to ensure that the concatenated value returned does not have
inadvertent leading or trailing spaces

=IF(ISNA(VLOOKUP(...),"",VLOOKUP(...))
The IF() and ISNA() functions are used together to trap errors when there is
no exact match found (eg: any names in Sheet 1 that do not match those in
RefTable) to return blanks in col B
 
Sample file sent to you, Chris !

The errors you experienced (wrong returns by the vlookup)
is probably because the names in RefTable (sheet 2)
also contained additional invisible spaces
which prevented the correct match by
vlookup in sheet 1.

So, you should also apply TRIM() on the original names
col in RefTable, and then use this new col (with
the TRIM() formula) as the lookup reference col in
RefTable

Take a look at the sample file.

It should work for you now

Feedback further in this thread.

My yahoo email account is 99.99% kaput,
unable to receive bona-fide email
because of the incessant virus-sending
large-email !

cheers
 
Back
Top