LinkBack URL
About LinkBacks
see sample file, i need a formula to do like B column,
I WANT MAKE LIKE B COLUMN, COMPARE A COLUMN DATA IN C,D COLUMN AND ALL DATA SHOULD BE IN B COLUMN..
see sample file, i need a formula to do like B column,
I WANT MAKE LIKE B COLUMN, COMPARE A COLUMN DATA IN C,D COLUMN AND ALL DATA SHOULD BE IN B COLUMN..
Hi,
without UDF
=IFERROR(VLOOKUP(TRIM(MID(SUBSTITUTE(A2,"|",REPT(" ",100)),1,100))+0,$C$2:$D$100,2,0),"")&IFERROR("|"&VLOOKUP(TRIM(MID(SUBSTITUTE(A2,"|",REPT(" ",100)),100,100))+0,$C$2:$D$100,2,0),"")&IFERROR("|"&VLOOKUP(TRIM(MID(SUBSTITUTE(A2,"|",REPT(" ",100)),200,100))+0,$C$2:$D$100,2,0),"")&IFERROR("|"&VLOOKUP(TRIM(MID(SUBSTITUTE(A2,"|",REPT(" ",100)),300,100))+0,$C$2:$D$100,2,0),"")
Regards
-----------------------------------------------------
At Excelforum, you can say "Thank you!" by clicking the star icon ("Add Reputation") below the post.
Please, mark your thread [SOLVED] if you received your answer.
Thanks for reply, its working, but i have many data in C,and D column, if i insert all data in C,D column then not working properly. many data not showing, now am using these through text to column function,
export all A column data into many column using text to column function, after that i look individually in every column to sn code and service column,.. its too long process, so only i asked this question.
Last edited by vengatvj; 11-09-2013 at 12:23 PM.
Hi, the formula looks for only the first 4 matches, in order to extract the fifth one add to the formula a new segment
&IFERROR("|"&VLOOKUP(TRIM(MID(SUBSTITUTE(A2,"|",REPT(" ",100)),400,100))+0,$C$2:$D$100,2,0),"")
------------------
You have also to adjust VLOOKUP range: maybe c2:d1000.
Maybe I'd need another sample file.
Regards
dear buddy, your formula is working good, but, not working when i paste any data in A, C,D column, i dont know what to do, i have sensitive data, thats y i cant able to give in this thread.
Hi,
maybe you have only to adjust vlookup references. Example: here vlookup will check for exact matches in range C2:D3000
IFERROR(VLOOKUP(TRIM(MID(SUBSTITUTE(A2,"|",REPT(" ",100)),1,100))+0,$C$2:$D$3000,2,0),"")&IFERROR("|"&VLOOKUP(TRIM(MID(SUBSTITUTE(A2,"|",REPT(" ",100)),100,100))+0,$C$2:$D$3000,2,0),"")&IFERROR("|"&VLOOKUP(TRIM(MID(SUBSTITUTE(A2,"|",REPT(" ",100)),200,100))+0,$C$2:$D$3000,2,0),"")&IFERROR("|"&VLOOKUP(TRIM(MID(SUBSTITUTE(A2,"|",REPT(" ",100)),300,100))+0,$C$2:$D$3000,2,0),"")
Hope it helps
-----------------------
In example attached I've stretched the formula to six vlookups
Last edited by canapone; 11-09-2013 at 01:01 PM.
Ya now its working great , thank you very very very much dear .... Congrats..
Hi,
thanks for your kind feedback
इटली से नमस्ते
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks