Look for the 6th and 7th character in a cell

[Tartesos]

Hello again.

I was wondering if there is a way, using a formula, to look in a cell and depending on the carachter on the 6th and or 7th place to output a specific information in another cell.

Lets say we have a number, REF345678567.

I need to have a formula that will look that number and when it finds the number 5 (6th carac.) it will fill another cell with information from another columm.

It needs to look also for the 7th carac., in this case 6, and put information on another cell from a columm.

Can anyone help me with this ??

Thanks and regards.

 

[Tartesos]

Thanks again Aladin, I will try this tomorrow and will let you know, thanks so mucth for your help.

 

[Tartesos]

Is not working, it gives an error ... "If you are not trying to enter a formula avoid using = etc ..." I checked the caract. and looks correct but is not working. This is what I have:

=IF(ISNUMBER(LOOKUP(9.99999999999999E+307,FIND(data!$B$4:$B$2488,imput!B4))
,T(LOOKUP(9.99999999999999E+307,FIND(data!$B$4:$B$2488,imput!B4),data!$AH$4:$AH$2488)),"")

Maybe you can see if I did something wrong.

 

[Tartesos]

Is not working, it gives an error ... "If you are not trying to enter a formula avoid using = etc ..." I checked the caract. and looks correct but is not working. This is what I have:

=IF(ISNUMBER(LOOKUP(9.99999999999999E+307,FIND(data!$B$4:$B$2488,imput!B4))
,T(LOOKUP(9.99999999999999E+307,FIND(data!$B$4:$B$2488,imput!B4),data!$AH$4:$AH$2488)),"")

Maybe you can see if I did something wrong.

I was also wondering if anyone knows a formula that will do this:
In sheet 2 I have a columm wiht all the ref. numbers from A1 to A10 (lest say). On that same sheet , but on columm B1:B10, there is a code number that is different for each ref. number on A1:A10.

In sheet 1 we have from A1:A10 where I can type a ref. number.

What Im looking for is a formula that will look on the sheet one and when you enter a number (A1:A10 sheet one) it will check that number and if it finds a 5 on the 9th place it will output the information from the B1:B10 of sheet 2 in the B1:B10 on sheet 1

So what I look for is the formula that will be able to check an entry number on A1 to A10 and if it finds the number 5 on the 9th place of that number it will get data from B1:B10 from the sheet 2 and put it on the B1:B10 on sheet 1. Is this possible??

 

[Tartesos]

Anyone?

 

[Aladin Akyurek]

Is not working, it gives an error ... "If you are not trying to enter a formula avoid using = etc ..." I checked the caract. and looks correct but is not working. This is what I have:

=IF(ISNUMBER(LOOKUP(9.99999999999999E+307,FIND(data!$B$4:$B$2488,imput!B4))
,T(LOOKUP(9.99999999999999E+307,FIND(data!$B$4:$B$2488,imput!B4),data!$AH$4:$AH$2488)),"")

Maybe you can see if I did something wrong.

I was also wondering if anyone knows a formula that will do this:
In sheet 2 I have a columm wiht all the ref. numbers from A1 to A10 (lest say). On that same sheet , but on columm B1:B10, there is a code number that is different for each ref. number on A1:A10.

In sheet 1 we have from A1:A10 where I can type a ref. number.

What Im looking for is a formula that will look on the sheet one and when you enter a number (A1:A10 sheet one) it will check that number and if it finds a 5 on the 9th place it will output the information from the B1:B10 of sheet 2 in the B1:B10 on sheet 1

So what I look for is the formula that will be able to check an entry number on A1 to A10 and if it finds the number 5 on the 9th place of that number it will get data from B1:B10 from the sheet 2 and put it on the B1:B10 on sheet 1. Is this possible??

I'm sorry... A paren is missing:

=IF(ISNUMBER(LOOKUP(9.99999999999999E+307,FIND(data!$B$4:$B$2488,imput!B4)))
,T(LOOKUP(9.99999999999999E+307,FIND(data!$B$4:$B$2488,imput!B4),data!$AH$4:$AH$2488)),"")

 

[Tartesos]

Thanks Aladin that was the problem with the formula. Thanks again !!

Regards.

Doei

 

[Aladin Akyurek]

Thanks Aladin that was the problem with the formula. Thanks again !!

Regards.

Doei

Great that it's all sorted out. Thanks for the feedback.

 

[Tartesos]

Aladin meaby you can help me with this one.

Ok, this is he formula im using:
=IF(ISNUMBER(LOOKUP(9.99999999999999E+307,FIND(data!$B$4:$B$2488,imput!B133))),T(LOOKUP(9.99999999999999E+307,FIND(data! $B$4:$B$2488,imput!B133),data!$AH$4:$AH$2488)),"")

So, when i type a ref. number on B133 on the imput sheet, this formula should go to AH4:AH2488 and select the correct output data that will be display on the correct cell(AH 133) from the imput sheet.
This is not hapening. This formula is also use from 4 to 2488.
Is true that the numbers that are in the imput cells 133 and 134 are the same but the information on the AH is not the same. In our company we can use duplicates ref. number but there is a number of differences that are found on the AH column from the data sheet.

But what is also very weird is that if I add the information on the cell 134 from the AH column on the data sheet, that information will be display on the 133 and 134 with the same value, the one on the AH134.
I was wondering if there is anything it may be wrong on the formula or it is something to do with the fact that the numbers are duplicate.
anyone can bring some light to this ? ...

Thanks
<!-- / message -->

 

[Tartesos]

anyone>?

 

[Aladin Akyurek]

Aladin meaby you can help me with this one.

Ok, this is he formula im using:
=IF(ISNUMBER(LOOKUP(9.99999999999999E+307,FIND(data!$B$4:$B$2488,imput!B133))),T(LOOKUP(9.99999999999999E+307,FIND(data! $B$4:$B$2488,imput!B133),data!$AH$4:$AH$2488)),"")

So, when i type a ref. number on B133 on the imput sheet, this formula should go to AH4:AH2488 and select the correct output data that will be display on the correct cell(AH 133) from the imput sheet.
This is not hapening. This formula is also use from 4 to 2488.
Is true that the numbers that are in the imput cells 133 and 134 are the same but the information on the AH is not the same. In our company we can use duplicates ref. number but there is a number of differences that are found on the AH column from the data sheet.

But what is also very weird is that if I add the information on the cell 134 from the AH column on the data sheet, that information will be display on the 133 and 134 with the same value, the one on the AH134.
I was wondering if there is anything it may be wrong on the formula or it is something to do with the fact that the numbers are duplicate.
anyone can bring some light to this ? ...

Thanks
<!-- / message -->

Just to make sure:

The formula is in:

AH133

a) imput!B133 houses a ref number (Care to post this value?)

b) imput!B144 houses the same ref number (as imput!B133).

Are you saying that AH133 for (a) and AH134 for (b) both return the same result, while they should be different?