Using If function to check condition in a range

[bmbalamurali]

Hi All,

I was trying to get some value using Match and If function, in this i have a doubt and clarify me how it can be done.

My question is if i have some text values from A1 to A5.

BOX
CYCLE A
RUBBER S: GHY
TEXT
HURTY

I need to check a condition from A1 to A5 whether it met that condition.

My formula is =If(A1:A5="RUBBER S: GHY",1,0) and its showing wrong answer or error.

I think am clear in my question. Waiting for reply. Thanks.

[MarkinTX]

Does this get you what your looking for?
It counts how many times the input is found in the range

Please Login or Register  to view this content.

[Tony Valko]

Here's one way...

Array entered**:

=--OR(A1:A5="RUBBER S: GHY")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

[gmsninja]

Hi,
You could also use
=IF(COUNTIF(A7:A11,"RUBBER S: GHY")>0,1,0)

[bmbalamurali]

Thanks.... MarkinTx, Tony Valko, gmsninja.

Am not clear in explaining my doubt, below i have clearly explined my question

A	B	C
BOX
RUBBER S: GHY
CYCLE A
TEXT
HURTY
	Daily
Price	45
Denom	56
Margin	75
Bargain	85

I have text data in A column and its numeric data in b column, i need a formula to get the value from col B corresponding to price.
so i used the below formula

Please Login or Register  to view this content.

and am getting the answer fine....
here the challenge is i need the match index function to look for value only if A1:A6 contains the two text in it ie: "RUBBER S: GHY" and "CYCLE A"

so i thought of using IF function to check whether the range A1:A6 contains the both text, then ithe match index function should look value and show....

but i cant know how to get this done... may be some other forumula can be used i dont know...

Pls help in this challenge....

Note: daily i need to do this, the Text "RUBBER S: GHY" and "CYCLE A" and "Price"doesn't change only the Value in the col B changes, hence i need this formula to accomplish the task of getting the value based on that presence of that text.


Thanks in Advance...

[Tony Valko]

Maybe this...

=IF(ISNUMBER(MATCH("RUBBER S: GHY",A1:A6,0)+MATCH("CYCLE A",A1:A6,0)),INDEX(B1:B13,MATCH("Price",A1:A13,0)),"")

[bmbalamurali]

Hi Tony,

Thank you it works well.... excel is a magic..... also i understood t\how the formula works...

I have attached a sample excxel file with data where i used the ablove formula arrived the result.

The another challenge i face is from A1:A13 i had a data and same has been verified and result is achived through formula and same wise i have more data under it from A13:A22, A24:A35 and A37:A46...

in this excel i updated the formula in sheet2 and if i paste the data in sheet1 i get the answers in sheet2...

the challenge i face is the data availabilty that is A1:A13 may vary A1:A8 and so on so that in some time chance i can a get a wrong answer when my data is not uniform... daily the d
data may vary... i need solution for that...

but that Page1, Page2, Page3 are uniform that comes in data every time... shall we use that as a key thing and work out in any way.... i think i gave a good challenge... if am not clear please come back , but damn sure i will get a soluution right soon....

Thanks for you time in advance...

[Tony Valko]

This is going to be very difficult with your data structure.

Is the particular cycle always within the same page group? For example, is Cycle A always found within the Page 1 group? Is Cycle B always found within the Page 2 group? Etc., etc.

[bmbalamurali]

As i provided a sample i can't give all data but there are more pages up to 21 or 22..

Yes Tony, Cycle A always found in page 1,2,3,4,5,6 groups and Cycle B in page 1,2,3,4,5,6 . but the text "RUBBER S: GHY" varies in every page group...

Really excited in knowing how this challenge is getting resolved? Thanks for your time....

[Tony Valko]

Cycle A always found in page 1,2,3,4,5,6 groups and Cycle B in page 1,2,3,4,5,6 . but the text "RUBBER S: GHY" varies in every page group...

To be more clear...

Cycle A will always be within the group Page 1 only? Cycle A will never be in a different group?

Cycle B will always be within the group Page 2 only? Cycle B will never be in a different group?

Cycle C will always be within the group Page 3 only? Cycle C will never be in a different group?

Etc.

Etc.

Etc.

[bmbalamurali]

Hi Tony,

To be more clear...

Cycle A will always be within the group Page 1 only? Cycle A will never be in a different group?
No, there are several pages upto 18 or 22 as i said and Cycle A appears in page1, page2... upto page 6 group.... the text "RUBBER S: GHY" varies in every page of Cycle A group ie: upto 6 pages like "Cycle A" & "RUBBER S: GHY" in page 1, "Cycle A" & "GHS: YHU"in page 2, "Cycle A" & "HECTOR: GHJ" in page 3.... upto page 6

Cycle B will always be within the group Page 2 only? Cycle B will never be in a different group?
No, As same as Above Cycle B appears in page7, page8, page9... upto page 12. and the text in first 6 pages of cycle A group repeats again in next 6 pages of Cycle B....

ie: "Cycle B" & "RUBBER S: GHY" in page 7, "Cycle B" & "GHS: YHU"in page 8, "Cycle B" & "HECTOR: GHJ" in page 9.... upto page 12

Cycle C will always be within the group Page 3 only? Cycle C will never be in a different group?
Cycle C also same as A and B cycle from page 13 to 18, with same text
Etc.

Etc.

Etc.

IF AM NOT STILL CLEAR PLS COME BACK WILL TAKE TIME AND MAKE A NEW DATA FILE WITH DETAILS AND SEND TOMORROW?

Thanks....

[Tony Valko]

Sorry, but I'm totally confused with this.

Can you post a SMALL sample file with REAL data?

SMALL = about 50 rows worth of data.

The data structure in your previous sample file is not conducive to easy analysis.

[bmbalamurali]

Hi Tony,

Below i have attached the sample excel file with correct data stucture... sorry i cant able to explain it in 50 rows so i have made it with about 219 rows...

Excitingly waiting for excel magic..... Thanks in advance...

[Tony Valko]

Ok, in your file the lookup value is always immediately above the cycle.

This works based on that observation.

Enter this array formula** in the Price sheet cell D6:

=OFFSET(INDEX(Data!$A:$A,MATCH(1,IF(Data!$A$1:$A$217=$C6,IF(Data!$A$2:$A$218=D$5,1)),0)),MATCH("Price",Data!$A$218:INDEX(Data!$A:$A,MATCH(1,IF(Data!$A$1:$A$217=$C6,IF(Data!$A$2:$A$218=D$5,1)),0)),0)-1,1)

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Copy across to F6 then down as needed.

Here's your file with this implemented.

Bala(2).xlsx

[bmbalamurali]

Hi Tony,

Wow! many thanks..... but i'm upset because i tried to understand this complex formula and failed in all attempts.

Formula doesn't contains Cycle A,B,C or Text "RUBBER S: GHY", etc details... then how its getting the answer correct....??? and also it has blank cells $C6, D$5 and all.... how its....

Can you pls tell me which part of this formula doing what?? sorry to bother precious time of yours... need your explanation so that i can do and succeed in these type of excel challenges in future..

Thanks for time and consideration through out this post...

Waiting for your reply...

[Tony Valko]

=OFFSET(INDEX(Data!$A:$A,MATCH(1,IF(Data!$A$1:$A$217=$C6,IF(Data!$A$2:$A$218=D$5,1)),0)),MATCH("Price",Data!$A$218:INDEX(Data!$A:$A,MATCH(1,IF(Data!$A$1:$A$217=$C6,IF(Data!$A$2:$A$218=D$5,1)),0)),0)-1,1)

The section highlighted in red returns the cell address for the combination of lookup value and cycle number. For example, if we want to lookup KRIB: GHI and CYCLE A the red section returns the cell address Data!A23.

We use that cell address as the starting point from which we want o find the word "Price".

=OFFSET(INDEX(Data!$A:$A,MATCH(1,IF(Data!$A$1:$A$217=$C6,IF(Data!$A$2:$A$218=D$5,1)),0)),MATCH("Price",Data!$A$218:INDEX(Data!$A:$A,MATCH(1,IF(Data!$A$1:$A$217=$C6,IF(Data!$A$2:$A$218=D$5,1)),0)),0)-1,1)

We find the word "Price" and return the result from the corresponding cell one column to the right.

Cells C6 and D5 contain the lookup value and the cycle number.

C6 = lookup value = RUBBER S: GHY
D5 = cycle number = CYCLE A

[bmbalamurali]

Hi Tony,

will try and come back....

Thanks for your time...