Thread: Data - Concatenate If Vlookup

AIM/ Problem ›—›› We need to get a total of 20 digits 'Final No.' in the next column by using below conditions and restrictions. The 'Final No.' must be appeared as following:

Examples: 00MR_No.000KP_No.

Please check the whole problem in detail with my approach in the attached MS Excel workbook, help me out!

Thanks in Advance!

vlookup needs to be
=VLOOKUP(--MID(D2,1,1),Ln_number,2,FALSE) or format your Ln_number range as text before entering values in it as text


--MID(D2,1,1) could simply be --left(d2)
d2 could be shortened to
=IF(OR(LEN(B2)<4,LEN(B2)>8),"",TEXT(B2,"00000000"))
e2
=IF(OR($B2="",LEN($B2)<4,LEN($B2)>8),"",LEN($D2))
then i got confused!

Hey martindwilson, Thanks for ur quick reply!

I appreciate ur short tricks, but wht made you confuse in this, plz tell.

u told:

vlookup needs to be
=VLOOKUP(--MID(D2,1,1),Ln_number,2,FALSE) or format your Ln_number range as text before entering values in it as text


--MID(D2,1,1) could simply be --left(d2)

Can u plz tell me how to use these - - (double minus sign together)? when I used ur this vlookup formula, data appeared ....Thanks!

format your Ln_number range as text before entering values in it as text

I don't get ur this above statement exactly. do u want me to keep this 'Ln_number' range in to the number or general or text format (by using Ctrl+1) ? Wht was there in the sheet, which was stopping me to get the desired output?

SunOffice, please don't use chat room speak in this forum.

mid or left return text values. so left(123) = 1 but is text
use of -- changes that to a real number . you can also use left(a1)+0 or left(a1)*1 or value(left(a1))
your lookup table is constructed of real numbers so that's why vlookup couldn't find a match
if you had formatted the cells in the table as text BEFORE entering values anything entered would be text and the vlookup would have worked as it stands

...?? :O teylyn!

sorry, I'm very new to this website, I'll take care of all the rules further. btw u mean to asking to repliers in general or something else?

I've just tried to solve the problem as per the worksheet - AIM (Object), I've almost got my desired solution (column H), but still need your help guys to give it the exact final solution (as per column J formula says), which shows a msg too if any condition fails.

I don't know that why is the formula in column 'J' giving me the same result as column 'H', not displaying the error msg - "Insert a correct MR No." ?

Note — As I've got the 'Final No.' in column H and J based on columns D and E, now wht I just want more is;

If LEN($B4)<4,LEN($B4)>8,LEN($B4)<15,LEN($B4)>15

display a error msg too in the 'Final result' columns (plz check the formula in the column 'J' for detailed requirments).

Plz help me out and correct the calculation.

Thank in Advance!