PDA

View Full Version : Lookup



michaelm702
10-12-2006, 12:27 AM
I have a excel with four columns. I want the macro to update the column D with the abbreviations for column C elements. The macro will fetch abbreviations from column B. I have sample elemnts in row 3 & 4. I want similar results in Row 3,4,5 using macro. I am attaching the excel.

CCkfm2000
10-12-2006, 01:11 AM
is the format always the same in row 3?

CCkfm2000
10-12-2006, 01:21 AM
sorry, should have asked you

is the format always the same in column C?

e.g 2_2_3 or will it change to 12_1_999

CCkfm2000
10-12-2006, 01:32 AM
ok this is what i got for you so far

=VLOOKUP(LEFT(C2,1),A:B,2,FALSE)&" "&VLOOKUP(LEFT(RIGHT(C2,3),1),A:B,2,FALSE)&" "&VLOOKUP(RIGHT(C2,1),A:B,2,FALSE)

paste the above code in cell d2 and copy down.

still need some more work.

CCkfm2000
10-12-2006, 04:22 AM
paste this funtion in cell d2 and copy down

=VLOOKUP(LEFT(C2,IF(ISERROR(FIND("_",C2,1)),LEN(C2),FIND("_",C2,1)-1)),A:B,2,FALSE)&" "&VLOOKUP((TRIM(IF(ISERROR(FIND("_",C2,1)),C2,MID(C2,FIND("_",C2,1)+1,IF(ISERROR(FIND("_",C2,FIND("_",C2,1)+2)),LEN(C2),FIND("_",C2,FIND("_",C2,1)+2))-FIND("_",C2,1)-1)))),A:B,2,FALSE)&" "&VLOOKUP(TRIM(RIGHT(C2,LEN(C2)-IF(ISERROR(FIND("_",C2,FIND("_",C2,FIND("_",C2,1)+2))),LEN(C2),FIND("_",C2,FIND("_",C2,FIND("_",C2,1)+2))*1))),A:B,2,FALSE)


took a long :doh: time but think it'll work if not help.

Bob Phillips
10-12-2006, 04:50 AM
I had to coerce it to get it to work as my column A was n umbers

=VLOOKUP(--LEFT(C2,IF(ISERROR(FIND("_",C2,1)),LEN(C2),FIND("_",C2,1)-1)),A:B,2,FALSE)&" "&
VLOOKUP(--(TRIM(IF(ISERROR(FIND("_",C2,1)),C2,MID(C2,FIND("_",C2,1)+1,IF(ISERROR(FIND("_",C2,FIND("_",C2,1)+2)),LEN(C2),FIND("_",C2,FIND("_",C2,1)+2))-FIND("_",C2,1)-1)))),A:B,2,FALSE)&" "&
VLOOKUP(--TRIM(RIGHT(C2,LEN(C2)-IF(ISERROR(FIND("_",C2,FIND("_",C2,FIND("_",C2,1)+2))),LEN(C2),FIND("_",C2,FIND("_",C2,FIND("_",C2,1)+2))*1))),A:B,2,FALSE)

Bob Phillips
10-12-2006, 04:57 AM
An alternative solution



=VLOOKUP(--LEFT(C2,FIND("_",C2)-1),A:B,2,FALSE)&" "&
VLOOKUP(--MID(C2,FIND("_",C2)+1,FIND("_",C2,FIND("_",C2)+1)-FIND("_",C2)-1),A:B,2,FALSE)&" "&
VLOOKUP(--MID(C2,FIND("~",SUBSTITUTE(C2,"_","~",LEN(C2)-LEN(SUBSTITUTE(C2,"_",""))))+1,255),A:B,2,FALSE)

michaelm702
10-12-2006, 07:20 AM
XLD - Your formula worked for the excel sheet provided by me but I can have something like 12_1_100 in column C so for this the formula wont work.

CCkfm2000
10-12-2006, 07:53 AM
hi michaelm702,

have you look at my code?

i've attached a copy of the file for you.

Bob Phillips
10-12-2006, 07:54 AM
XLD - Your formula worked for the excel sheet provided by me but I can have something like 12_1_100 in column C so for this the formula wont work.

That formula works for me, what do you get?

michaelm702
10-13-2006, 04:42 AM
XLD & CCKfm both of your formulas worked thanks. XLD you had --LEFT can you pls tell me why we use "--".

Bob Phillips
10-13-2006, 05:19 AM
As I mentioned to the other guy, 1 could be a text one, or numeric 1. Because all LEFT,RIGHT, MID functions return a string, I used -- to coerce it to a number, so the lookup works.

patrickab
10-13-2006, 07:19 AM
Try this:

=VLOOKUP(--LEFT(C2,FIND("_",C2,1)-1),data,2)&" "&VLOOKUP(--MID(C2,FIND("_",C2,1)+1,FIND("_",C2,FIND("_",C2,1)+1)-FIND("_",C2,1)-1),data,2,FALSE)&" "&VLOOKUP(--RIGHT(C2,LEN(C2)-FIND("_",C2,FIND("_",C2,1)+1)),data,2,FALSE)

It's horrendous but it does work.

The alternative is to use Data/TexttoColumns with the delimiter set as and underscore '_' and then VLOOKUP() each value found in the three columns and concatenate the result. That requires 3 extra columns but the result is easier to see how it's been achieved and can also be error checked by looking at the three helper columns (which can be hidden if required). Using this technique the formula would be:

=VLOOKUP(F2,data,2,FALSE)&" "&VLOOKUP(G2,data,2,FALSE)&" "&VLOOKUP(H2,data,2,FALSE)

If course error checking can be built in using and =IF(ISERROR(VLOOKUP()),"",VLOOKUP()) type of syntax. That will work but will make change formula from horrendous to truly horrendous for the first formula to complex for the second.

Hope all that helps. Meanwhile here's a link to a file with it all in:

http://www.asdy88.dsl.pipex.com/Experts%20Exchange/Lookup01.xls

Sorry about the insentive address but that's the sub-directory I use for my uploads to my ISP's server!

Patrick