format issue..vlookup doesn't work

[Spiru]

Hello,

I have concatenated like in the example below the document number and the index from the first table in order to bring from the second table the information that I need with vlookup. The formula that I'm using is

=IF(A12=A2,VLOOKUP(A12,$A$2:$C$6,3,0),VLOOKUP(B12,$B$2:$C$6,2,0))

. In this particular example it works, but in another file it doesn't so I'm thinking it must be something wrong with the format. Also when I'm trying to find the concatenated cell with find it can't find it, but if I search for the document number and the Index individually it works.

Could you kindly advise on what can cause this error? (I've converted the cells that have numbers to numbers, I've selected the whole sheet and made sure that its "General" selected and not number or text)

[TABLE="width: 233"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD]Doc Number[/TD]
[TD]Index[/TD]
[/TR]
[TR]
[TD]765abc[/TD]
[TD]765[/TD]
[TD]abc[/TD]
[/TR]
[TR]
[TD]897cdc[/TD]
[TD]897[/TD]
[TD]cdc[/TD]
[/TR]
[TR]
[TD]321abc[/TD]
[TD]321[/TD]
[TD]abc[/TD]
[/TR]
[TR]
[TD]876cdc[/TD]
[TD]876[/TD]
[TD]cdc[/TD]
[/TR]
[TR]
[TD]563abc[/TD]
[TD]563[/TD]
[TD]abc[/TD]
[/TR]
</tbody>[/TABLE]


Second table

[TABLE="width: 367"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD] [/TD]
[TD]Doc Number[/TD]
[TD]Customer [/TD]
[TD]Index[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]765abc[/TD]
[TD]765[/TD]
[TD]Cola[/TD]
[TD]abc[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]897cdc[/TD]
[TD]897[/TD]
[TD]Capy[/TD]
[TD]cdc[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]321 [/TD]
[TD]321[/TD]
[TD]fanta[/TD]
[TD]76[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]876 [/TD]
[TD]876[/TD]
[TD]cola[/TD]
[TD]45[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]563abc[/TD]
[TD]563[/TD]
[TD]sprite[/TD]
[TD]abc[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Thanks a lot,

 

[Aladin Akyurek]

@ Aladin

they don't seem to work..they bring back only na
[...]

Did you apply control+shift+enter as required?

It works now...I've concatenated the two cells in another column and it works..don't know why..thanks a lot for your help

Care to post what works?

 

[Spiru]

Hi Aladin,

Yes, I've pressed CTRL+SHIFT+ENTER but no results

[COLOR=#333333]=IF(A12=A2,VLOOKUP(A12,$A$2:$C$6,3,0),VLOOKUP(B12,$B$2:$C$6,2,0))[/COLOR]

I've used this formula that I've tried the first time only now instead of concatenating the Document number and the index columns in cell A12, I've concatenated them in B12 and it worked

Did you apply control+shift+enter as required?



Care to post what works?

 

[Aladin Akyurek]

Hi Aladin,

Yes, I've pressed CTRL+SHIFT+ENTER but no results

This surprises me! While concatenation seems to work...

[COLOR=#333333]=IF(A12=A2,VLOOKUP(A12,$A$2:$C$6,3,0),VLOOKUP(B12,$B$2:$C$6,2,0))[/COLOR]

I've used this formula that I've tried the first time only now instead of concatenating the Document number and the index columns in cell A12, I've concatenated them in B12 and it worked[/QUOTE]

Maybe I wasn't using the right return range...

 

[sheetspread]

but when I try to find on the concatenated column in the first table the number from the second table it says it can't be found although its there..

In the Look in dropdown, you have to choose values. If the default is Formulas then ctrl-f won't find the concatenated value.

Also looking up text from text is easier than number from number when there's imports/concats/left/right/mid parsing because usually everything is formatted as text.

Anyway I'm glad you got it solved, post back if you need more help.....

 

[Spiru]

thanks a lot for your support

In the Look in dropdown, you have to choose values. If the default is Formulas then ctrl-f won't find the concatenated value.

Also looking up text from text is easier than number from number when there's imports/concats/left/right/mid parsing because usually everything is formatted as text.

Anyway I'm glad you got it solved, post back if you need more help.....