PDA

View Full Version : Solved: letter index



oleg_v
01-16-2010, 04:02 PM
Hello
I have this function line:
=INDEX(Sheet1!$C:$C,MATCH(A2,Sheet1!$B:$B,0)+2)
I need to replace the "+2" to "+3" if the cell "A2" contains the letter"t" among
other letters and numbers in the cell.

Thanks

Oleg

Bob Phillips
01-16-2010, 04:55 PM
try

=INDEX(Sheet1!$C:$C,MATCH(A2,Sheet1!$B:$B,0)+2+ISNUMBER(SEARCH("t",A2)))

Aussiebear
01-16-2010, 05:11 PM
Bob, I had envisaged something more like this:

=If(IsNumber(Search("t",A2)),INDEX(Sheet1!$C:$C,Match(A2,Sheet1$B$B,0)+3, INDEX(Sheet1!$C:$C,Match(A2,Sheet1$B$B,0)+2)

On the basis that if it finds the value "t" in A2 it changes the function to +3 else remains at the original function. I'm guessing I must be wrong then.

oleg_v
01-16-2010, 05:11 PM
hi thanks

say where do i see that instead of"+2" is "+3"?

oleg_v
01-16-2010, 05:13 PM
Bob, I had envisaged something more like this:

=If(IsNumber(Search("t",A2)),INDEX(Sheet1!$C:$C,Match(A2,Sheet1$B$B,0)+3, INDEX(Sheet1!$C:$C,Match(A2,Sheet1$B$B,0)+2)

On the basis that if it finds the value "t" in A2 it changes the function to +3 else remains at the original function. I'm guessing I must be wrong then.

Hi
thanks for the quik replay
i will check it in a few minuts i think you that this is what i need

oleg_v
01-16-2010, 05:23 PM
hi

this is not working it gives me some excel error but i think the idea is exactly what meant

Aussiebear
01-16-2010, 05:35 PM
Post the error message. We can't see over your shoulder

Bob Phillips
01-16-2010, 05:43 PM
Bob, I had envisaged something more like this:

=If(IsNumber(Search("t",A2)),INDEX(Sheet1!$C:$C,Match(A2,Sheet1$B$B,0)+3, INDEX(Sheet1!$C:$C,Match(A2,Sheet1$B$B,0)+2)

On the basis that if it finds the value "t" in A2 it changes the function to +3 else remains at the original function. I'm guessing I must be wrong then.

Well, 3 is 2+1, so you don't need to test for the letters and repeat the core formula, just add 1 if t is in cell A2.

Bob Phillips
01-16-2010, 05:47 PM
Or to put it another way, you can simplify yours to

=INDEX(Sheet1!$C:$C,MATCH(A2,Sheet1!$B:$B,0)+2+IF(ISNUMBER(SEARCH("t",A2)),1,0))

which is the same as mine, I just use no IF statement

Bob Phillips
01-16-2010, 05:49 PM
hi

this is not working it gives me some excel error but i think the idea is exactly what meant

There are a few typos in Bear's answer

=IF(ISNUMBER(SEARCH("t",A2)),INDEX(Sheet1!$C:$C,MATCH(A2,Sheet1!$B:$B,0)+3),INDEX(Sheet1!$C:$C,MAT CH(A2,Sheet1!$B:$B,0)+2))

but mine is better.

oleg_v
01-16-2010, 05:51 PM
i fixed the error but the results are not what i need

when the cell "A2" contains the letter "t" this writes me N/A instead of activating the index function and when the cell does not contain the letter "t" it writes me "false" instead of activating the other index function.

Thanks

Aussiebear
01-16-2010, 06:50 PM
Please post your workbook

oleg_v
01-16-2010, 07:01 PM
HI
Attached the file in the sheet1 you can see the dimensions
in the sheet2 you can see the index function

Bob Phillips
01-17-2010, 09:50 AM
My formula works perfectly, although it should be 2.071 not 0.061 because 0.061 is 4 rows beyond, not 2.

Aussiebear
01-17-2010, 02:26 PM
My testing shows that the following formula works;

=IF(ISNUMBER(SEARCH("t",A2)),INDEX(Sheet1!$C:$C,MATCH(A2,Sheet1!$B:$B,0)+4) ,INDEX(Sheet1!$C:$C,MATCH(A2,Sheet1!$B:$B,0)+2))

My thanks to Bob for the hints

Bob Phillips
01-17-2010, 05:43 PM
My testing shows that the following formula works;

=IF(ISNUMBER(SEARCH("t",A2)),INDEX(Sheet1!$C:$C,MATCH(A2,Sheet1!$B:$B,0)+4) ,INDEX(Sheet1!$C:$C,MATCH(A2,Sheet1!$B:$B,0)+2))

My thanks to Bob for the hints

As does

=INDEX(Sheet1!$C:$C,MATCH(A2,Sheet1!$B:$B,0)+2+(ISNUMBER(SEARCH("t",A2))*2))

assuming an error on the original post, corrected later by the OP

Aussiebear
01-17-2010, 05:58 PM
Bob, I think the issue comes about because not enough information was provided in the initial post. I am firmly of the opinion the people should where ever possible attach a workbook. This way those trying to assist get to see a fuller picture. But then I'm preaching to the converted in your case.

oleg_v
01-17-2010, 11:27 PM
Thanks

In the futute i will provide more information and attach the workbook

great job every one and thanks a lot

Aussiebear
01-18-2010, 01:08 AM
Please mark the thread as "Solved" by using the Thread tools options just above the first post