Combination of IF and LEFT

Hello every one.

I need to chose (copy) an value in the column “B” having as reference the first 5 characters from the value of the cells from the column A.

Exemple.

Colone A **** Colone B

12345 aaaaa **** 11112
12312 bbbbb **** 1212312
35758 ccccc **** 12312312
12345aaaaa **** 11112
12312bbbbb **** 1212312
35758ccccc **** 12312312


I need to chose the value in the column B for the cells corresponding to the first five caracters (left) of the cells of the column A.

I tried with """ if ((left (A:A);5);12345; B:B)""" === """If the 5 first characters des values in the column A are equal to 12345, then chose the corresponding value in column B."""

An better exemple is in the attached spreadsheet.

But it doesn’t work.

I would really appreciate if you have any other idea for solving this problem.

Friendly
Sokol

Like this?

Please Login or Register  to view this content.

Hi Sokol, there are a few questions about the data. Are the values in column B text or numbers?

If numbers, try

=IF(1*LEFT(A1,5)=12345,B1,"")

if text, try

=IF(LEFT(A1,5)="12345",B1,"")

I have an inkling that this is not the complete picture. If this does not solve your issue, please post a data sample and explain in context.

cheers

Sokol,

regarding your last post ...

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

As I understood u want to lookup entire column A by first 5 symbols in and return coressponding value in column B. So

Please Login or Register  to view this content.

OR

Please Login or Register  to view this content.

This is array formula and needs to be entered with CTRL SHIFT ENTER. In this case I wouldnt advice to use entire column range.

Try

Please Login or Register  to view this content.

Where D2 is 83510

This is array formula and needs to be entered with CTRL SHIFT ENTER

Hi
I've uploaded