Solved: letter index

[oleg_v]

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]

try

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

[Aussiebear]

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]

hi thanks

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

[oleg_v]

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]

hi

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

[Aussiebear]

Post the error message. We can't see over your shoulder

[Bob Phillips]

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]

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]

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,MATCH(A2,Sheet1!$B:$B,0)+2))

but mine is better.

[oleg_v]

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]

Please post your workbook

[oleg_v]

HI
Attached the file in the sheet1 you can see the dimensions
in the sheet2 you can see the index function

[Bob Phillips]

My formula works perfectly, although it should be 2.071 not 0.061 because 0.061 is 4 rows beyond, not 2.

[Aussiebear]

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]

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]

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]

Thanks

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

great job every one and thanks a lot

[Aussiebear]

Please mark the thread as "Solved" by using the Thread tools options just above the first post