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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.