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,

 

[sheetspread]

Put value() around the number if the range is numbers, or text(expression,"0") if it's text.

A +0 can also convert text numbers to value and &"" can convert values to text

Changing the format isn't always enough if the data is imported from an external source, in which case you can recalculate by copying a blank cell then paste, special, add to the range.

 

[sheetspread]

wait if it's not finding the concatenated cell, are you sure they're identical, i.e. no hidden spaces?

 

[Spiru]

wait if it's not finding the concatenated cell, are you sure they're identical, i.e. no hidden spaces?

Hi sheetspread,

Yes the data is imported from an external source. I've tried using for both concatenates =text(B2&C2,0) and =text(B12&D12,0) but the the vlookup formula still doesn't work. if I try to use =value(B2&C2) I receive [TABLE="width: 138"]
<tbody>[TR]
[TD="width: 138, align: center"]#VALUE! .

[/TD]
[/TR]
</tbody>[/TABLE]
Also I've tried using =Trim(B2&C2) and =Trim(B12&D12) in order to eliminate hidden spaces but still no results

 

[Aladin Akyurek]

How about using the original values?

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

I​

[/td][td]

J​

[/td][td]

K​

[/td][/tr][tr][td]

1​

[/td][td] Doc Number[/td][td] Customer[/td][td] Index[/td][td] Doc Number[/td][td] Index[/td][td]CUS[/td][/tr]
[tr][td]

2​

[/td][td] 765[/td][td] Cola[/td][td] abc[/td][td] 765[/td][td] abc[/td][td]Cola[/td][/tr]
[tr][td]

3​

[/td][td] 897[/td][td] Capy[/td][td] cdc[/td][td] 897[/td][td] cdc[/td][td]Capy[/td][/tr]
[tr][td]

4​

[/td][td] 321[/td][td] fanta[/td][td] 76[/td][td] 321[/td][td] abc[/td][td]na[/td][/tr]
[tr][td]

5​

[/td][td] 876[/td][td] cola[/td][td] 45[/td][td] 876[/td][td] cdc[/td][td]na[/td][/tr]
[tr][td]

6​

[/td][td] 563[/td][td] sprite[/td][td] abc[/td][td] 563[/td][td] abc[/td][td]sprite[/td][/tr]
[/table]

In K2 control+shift+enter, not just enter, and copy down:

Either...

=IFNA(INDEX($B$2:$B$6,MATCH(I2,IF($C$2:$C$6=J2,$A$2:$A$6),0)),"na")

Or...

=IFNA(INDEX($B$2:$B$6,MATCH(I2,IF(ISNUMBER(SEARCH(J2,$C$2:$C$6)),$A$2:$A$6),0)),"na")

Or...

=IFNA(INDEX($B$2:$B$6,MATCH("*"&I2&"*",IF(ISNUMBER(SEARCH(J2,$C$2:$C$6)),$A$2:$A$6&""),0)),"na")

Which of the foregoing yields better or correct result if any?

 

[sheetspread]

Did you test that the cells are equal with =firstcell = secondcell ?

 

[Muhammad_Usman]

Its working

[/IMG]

 

[Spiru]

@ Aladin

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

@sheetspread

I've tried =firstcell = secondcell and it gives value TRUE, 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..

@Muhammad

in the example I gave it works but in another excel file that has the columns updated from an external source vlookup can't match the concatenated column in the first table with the concatenated column in the second one

 

[AliGW]

@Muhammad

in the example I gave it works but in another excel file that has the columns updated from an external source vlookup can't match the concatenated column in the first table with the concatenated column in the second one

If you are getting the #N/A value, then the match simply isn't happening. Try this on first the lookup cell on the first sheet and the match in the lookup array on the second sheet:

=LEN(cell_reference)

Do they give the same result?

 

[Spiru]

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