Left Function in Combination with IF, LOOKUP, MATCH, or INDEX?

[superwhoever]

Hello All,

I attempted to search the forums, but found the same information as google referenced. And so....I present you with (=MYIGNORANCEISKILLINGME). Column A in "Sheet 1" contains data in the form of (AAAA 111 2222). This data contains a repitition of the same 4 letter codes, but the 7 number values change. I am attempting to match these 4 letters to a second sheet "Freight Vendor Number & Carrier" that I have begun to compile the 4 letter codes and a corresponding "Freight Vendor Number & Carrier" that remains the same (true) for each individual 4 letter code. For example, AAAA always corresponds to 909909. I want to match "AAAA 111 2222" from sheet 1 with AAAA from the sheet named "Freight Vendor Number & Carrier" to formulate 909909 in a new column. Any help would be much appreciated.

Regards,

SUPER

[vlady]

You could post a sample of what you are doing sometimes it's hard to duplicate data.

When you are in Advanced reply, click on the paperclip to open the upload window.

View Pic

thanks.

[superwhoever]

Trying to generate the yellow highlighted cells.

[superwhoever]

Notice my 3 formulas that I attempted, but errored on. #VALUE x3!

[vlady]

left should be contained inside the vlookup
start with D4

=VLOOKUP(LEFT(A4,4),'Freight Vendor Number & Carrier'!$A$1:$B$6,2,FALSE)
i think much better with the trim function just to make sure...


=VLOOKUP(trim(LEFT(A4,4)),'Freight Vendor Number & Carrier'!$A$1:$B$6,2,FALSE)

[superwhoever]

WORKS! What did I do incorrectly? Would like to know my mistake for application in future functions.

[vlady]

your formula the underlined part

=IF(LEFT(A29,4),VLOOKUP(A29,'Freight Vendor Number & Carrier'!$A$1:$B$6,2,FALSE),"Did Not Work")


actually you can also use iferror for your message.

=iferror(VLOOKUP(LEFT(A4,4),'Freight Vendor Number & Carrier'!$A$1:$B$6,2,FALSE),"DID NOT WORK") <- with message

[superwhoever]

There is a problem with your formula. My letter combo of CWSW and numbers 557814. Letters SOUW generated the same numbers 557814. The number needs to match the 4 letters in the order they appear.

[vlady]

i don't see SOUW in the table...

[superwhoever]

It is not in the example, but on my worksheets SOUW on both sheets = 557904. CWSW = 557814. The formula is assigning both letter combos with 557814. That is a problem. As if it is finding the S and W as commons and assigning the same value.

[superwhoever]

I just noticed that my original spreadsheet has some of these (AAAA 1100 203) aligned at center of the cell, instead of aligned to the left. Would thos spaces be included in the trim(left(A4,4)?

[vlady]

if that doesn't solve the issue try

left(trim(A4),4) -> the trim will delete the spaces first if any then get the first four letters (left function)

[superwhoever]

I figured out that the columns being transfered were not properly matched. It was doing what is was supposed to, just in the wrong place. Thanks for your help. I may have more to come!!!!!

[vlady]

Your welcome and thanks for the star tap.