Concatenate and Vlookup

[Lynneth]

I am really puzzled. Could someone help, please?

I am using a lookup table, where I have had to use concatenate to merg
three cells into one. I have then copied the fomula down to all othe
cells

In another sheet, I am trying to vlookup that column of cells (I hav
named the range) but it does not recognise the cells in th
concatenated column.

I know my formula is OK, but it just does not recognise the

In fact, when I am on the sheet and do a simple Find, it doesn't se
the cells either.

Help!!

Thank you so much
Lynn

 

[Guest]

You may have mispelled the RangeName when creating it, or inadvertantly
inserted a leading space on the RangeName........(I just idd thei on my PDA
and it drove me nuts) <g>.........just re-do the RangeName........

Vaya con Dios,
Chuck, CABGx3

 

[Richard Buttrey]

I am really puzzled. Could someone help, please?

I am using a lookup table, where I have had to use concatenate to merge
three cells into one. I have then copied the fomula down to all other
cells

In another sheet, I am trying to vlookup that column of cells (I have
named the range) but it does not recognise the cells in the
concatenated column.

I know my formula is OK, but it just does not recognise the

In fact, when I am on the sheet and do a simple Find, it doesn't see
the cells either.

Help!!

Thank you so much
Lynne

Are the cells that you've concatenated, numbers? If so the resultant
cell is probably text. Are you specifiying text in your lookup value,
i.e. say "123" instead of 123?

Rgds

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

 

[Guest]

it sounds as though the cells are different somehow.

a quick check to to do a len() on one of your concatination cells and on
your lookup cell to see if there might be leading or trailing spaces.
another way to check it is to use = one of your conatenation cells
then highlight the cell reference in the formula bar and press F9
you will see any text surrounded by Quote marks
do the same for your lookup cell reference and see if there is any difference.

 

[Lynneth]

Are the cells that you've concatenated, numbers? If so the resultant
cell is probably text. Are you specifiying text in your lookup value,
i.e. say "123" instead of 123?

Rgds

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

I am only referring to a cell, not a name

I have copied the formula below

=IF(ISNA(VLOOKUP(C24,Code,5,FALSE)),((AC24)),VLOOKUP(C24,Code,5))

The formula is looking up C24 in the name range Code, and if it is no
there put in the figure in AC24, but if it is there put the value fro
column 5 in the named
range

I know that this formula works in other spreadsheets, but it is no
working with a concatenated cell and column

I hope this explains things a bit more clearly

Thanks very much

Lynn

 

[Lynneth]

Are the cells that you've concatenated, numbers? If so the resultant
cell is probably text. Are you specifiying text in your lookup value,
i.e. say "123" instead of 123?

Rgds

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

I have copied the formula below

=IF(ISNA(VLOOKUP(C24,Code,5,FALSE)),((AC24)),VLOOKUP(C24,Code,5))

The formula is looking up C24 in the name range Code, and if it is no
there put in the figure in AC24, but if it is there put the value fro
column 5 in the named
range

I know that this formula works in other spreadsheets, but it is no
working with a concatenated cell and column

I hope this explains things a bit more clearly

Thanks very much

Lynn

 

[Biff]

Hi!

Try this:

=IF(ISNA(VLOOKUP(C24,Code,5,FALSE)),AC24,VLOOKUP(C24,Code,5,FALSE))

Biff

 

[Richard Buttrey]

I am only referring to a cell, not a name

I have copied the formula below

=IF(ISNA(VLOOKUP(C24,Code,5,FALSE)),((AC24)),VLOOKUP(C24,Code,5))

The formula is looking up C24 in the name range Code, and if it is not
there put in the figure in AC24, but if it is there put the value from
column 5 in the named
range

I know that this formula works in other spreadsheets, but it is not
working with a concatenated cell and column

I hope this explains things a bit more clearly

Thanks very much

Lynne

What is the result of AC24? Something that looks like a number or is
it a text string?
If you type in another cell =ISTEXT(AC24) what answer do you get?
True or False
If you test the 5th column in your range code in the row which
contains the value you're looking up with the same =ISTEXT, do you get
the same True/False Answer.

Try wrapping your concatenated formula with " =Value"

i.e. if C24 contains say =C1&C2&C3 change it to
=Value(C1&C2&C3)


HTH
__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

 

[KL]

Hi Richard,

First of all, your lookup formula is inconsistent as it first checks for
exact coincidence:
VLOOKUP(C24,Code,5,FALSE)

but returns an approximate match:
VLOOKUP(C24,Code,5)

Secondly, if you are searching for an exact match then you could change your
lookup formula as follows:

=IF(ISNA(VLOOKUP(""&C24,Code,5,0)),((AC24)),VLOOKUP(""&C24,Code,5,0))

Thirdly , if you are searching for an approximate match (in a sorted range)
of a numerical value then you should change your lookup formula as follows:

=IF(ISNA(VLOOKUP(C24,Code,5)),((AC24)),VLOOKUP(C24,Code,5))

and replace your concatenation formula with the following:

=--(YourConcatenationFormula)

Regard,
KL