Include IF in formula

[Pat]

I want the following formula to return a result only if val! B1:B8 contains
the value 2

=INDEX(val!$C$1:$C$8,MATCH(cc$A1,val$A$1:$A$8,0))

Thank you

 

[Dave Peterson]

=if(countif(val!b1:b8,2)=0,"",yourformula)

I want the following formula to return a result only if val! B1:B8 contains
the value 2

=INDEX(val!$C$1:$C$8,MATCH(cc$A1,val$A$1:$A$8,0))

Thank you

 

[Aladin Akyurek]

Y2:

=MATCH($A1,val$A$1:$A$8,0)

X2:

=IF(ISNUMBER(Y2),INDEX(val!$C$1:$C$8,Y2),"")

X2 is the result cell.

 

[Pat]

Sorry but I quite don't understand how your solution will do what I want.
I entered

=MATCH($A1,val$A$1:$A$8,0)

into cc!Y2
and

=IF(ISNUMBER(Y2),INDEX(val!$C$1:$C$8,Y2),"")

into cc!X2

The results returned do not give the correct result.
I do not see any reference to val! B1:B8 in your formulas

Pat

 

[Pat]

I have tried your suggestion and it does not give the correct result. I
does not recognise the value 2 in val!$B$1:$B$8

=IF(COUNTIF(val!$B$1:$B$8,2)=0,"",INDEX(val!$C$1:$C$8,MATCH(cc!$A4,val!$A$1:$A$8,0)))

Pa

 

[Ragdyer]

You didn't comment on Dave's suggestion.
His will allow the formula to display a return if *any* cell in B1:B8
contains a 2.

Is that sufficient?

Your stipulation that <<"if val! B1:B8 contains the value 2">>
is not too clear as to whether or not *all* the cells in B1:B8 should
contain a 2.

If that be the case, simply adjust Dave's formula to:

=IF(COUNTIF(Val!B1:B8,2)<>8,"",INDEX(Val!$C$1:$C$8,MATCH(cc!A1,Val!$A$1:$A$8
,0)))

 

[Pat]

is not too clear as to whether or not *all* the cells in B1:B8 should
contain a 2.

Cells B1:B8 is a test range and only one or perhaps more of the cells will
contain 2

See also my earlier post to Dave.

 

[Aladin Akyurek]

Mea culpa. I missed that specification regarding val! B1:B8.

BTW, X2 and Y2 were just two arbitrary cells, used to split up the required
formula for a faster calculation.

You have the lookup value of interest in A1.

In C1 enter:

=IF(ISNUMBER(MATCH(2,val!$B$1:$B$8,0)),MATCH($A1,val$A$1:$A$8,0),"")

In B1 enter:

=IF(ISNUMBER(C1),INDEX(val!$C$1:$C$8,C1),"")

B1 is the result cell.

 

[Pat]

In C1 enter:

=IF(ISNUMBER(MATCH(2,val!$B$1:$B$8,0)),MATCH($A1,val$A$1:$A$8,0),"")

When this formula is filled down it returns an increment of 2, sorry but
this is not the result I am looking for.

Pat

 

[Ragdyer]

Hey Pat,

You just mentioned:
<<"When this formula is filled down it returns an increment of 2">>

*NONE* of these formulas were meant to be *filled down*!

If you want a "fill down" formula, try this:

=IF(AND(Val!A1=cc!$A$1,Val!B1=2),Val!C1,"")

 

[Pat]

Your formula will only return a result only if Val!B1=2 It needs to return
a result if any cell (and not necessarily B1) Val!B1:B8=2

 

[RagDyeR]

But you did say you wanted to "fill down".
This formula works row by row.

As you fill down, the relative cell references will adjust themselves
automatically.

If this doesn't work for you, perhaps you might try to re-state your
question, describing your data setup, since the posting of your "not
working" formula seems to *not* convey enough information for a viable
suggestion, evidenced here by *all* the failed attempts to help you.

 

[Pat]

I will go back to my original question

the value 2
=INDEX(val!$C$1:$C$8,MATCH(cc$A1,val$A$1:$A$8,0))

What follows is the test workbook I am using:

val!
A1 = car B1 = 1 C1 = T1
A2 = lorry B2 = 1 C2 = T4
A3 = lorry B3 = 2 C3 = T2
A4 = motorbike B4 = 1 C4 = T1
A5 = motorbike B5 = 2 C5 = T2
A6 = tractor B6 = 1 C6 = T2
A7 = motorbike B7 = 3 C7 = T4
A8 = car B8 = 2 C8 = T3

cc!
A1 = car B1 = T3
A2 = lorry B2 = T2
A3 = motorbike B3 = T2
A4 = tractor B4 =

The results in cc! B1:B4 are the result of a formula which I was trying to
obtain from my earlier posts. So in a nutshell I need a formula that will
return the correct answer from val! column C based on whether there is the
value 2 in the relative cell in val! column B

Hope this a bit clearer.
Pat

 

[RagDyer]

Try this array formula in B1 of sheet cc:

=INDEX(val!$C$1:$C$8,MATCH(1,(val!$A$1:$A$8=A1)*(val!$B$1:$B$8=2),0))

As an array formula, you must use CSE (<Ctrl> <Shift> <Enter>) instead of
just Enter.
If done correctly, this will *automatically* enclose the formula in curly
brackets ( {} ).

Then drag down to copy as far as needed.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


I will go back to my original question

the value 2
=INDEX(val!$C$1:$C$8,MATCH(cc$A1,val$A$1:$A$8,0))

What follows is the test workbook I am using:

val!
A1 = car B1 = 1 C1 = T1
A2 = lorry B2 = 1 C2 = T4
A3 = lorry B3 = 2 C3 = T2
A4 = motorbike B4 = 1 C4 = T1
A5 = motorbike B5 = 2 C5 = T2
A6 = tractor B6 = 1 C6 = T2
A7 = motorbike B7 = 3 C7 = T4
A8 = car B8 = 2 C8 = T3

cc!
A1 = car B1 = T3
A2 = lorry B2 = T2
A3 = motorbike B3 = T2
A4 = tractor B4 =

The results in cc! B1:B4 are the result of a formula which I was trying to
obtain from my earlier posts. So in a nutshell I need a formula that will
return the correct answer from val! column C based on whether there is the
value 2 in the relative cell in val! column B

Hope this a bit clearer.
Pat

 

[Aladin Akyurek]

[...]

What follows is the test workbook I am using:

val!
A1 = car B1 = 1 C1 = T1
A2 = lorry B2 = 1 C2 = T4
A3 = lorry B3 = 2 C3 = T2
A4 = motorbike B4 = 1 C4 = T1
A5 = motorbike B5 = 2 C5 = T2
A6 = tractor B6 = 1 C6 = T2
A7 = motorbike B7 = 3 C7 = T4
A8 = car B8 = 2 C8 = T3

In D1 on the Val sheet enter & copy down:

=A1&CHAR(127)&B1

cc!
A1 = car B1 = T3
A2 = lorry B2 = T2
A3 = motorbike B3 = T2
A4 = tractor B4 =

[...]

In B1 on the cc sheet enter & copy down:

=INDEX(val!$C$1:$C$8,MATCH(A1&CHAR(127)&2,val!$D$1:$D$8,0))

 

[Pat]

Thank you gentlemen, both your answers give the result I am looking for.
There is one change needed for both answers though, where there is no value
to return#N/A is returned

Pat

[...]

What follows is the test workbook I am using:

val!
A1 = car B1 = 1 C1 = T1
A2 = lorry B2 = 1 C2 = T4
A3 = lorry B3 = 2 C3 = T2
A4 = motorbike B4 = 1 C4 = T1
A5 = motorbike B5 = 2 C5 = T2
A6 = tractor B6 = 1 C6 = T2
A7 = motorbike B7 = 3 C7 = T4
A8 = car B8 = 2 C8 = T3

In D1 on the Val sheet enter & copy down:

=A1&CHAR(127)&B1

cc!
A1 = car B1 = T3
A2 = lorry B2 = T2
A3 = motorbike B3 = T2
A4 = tractor B4 =

[...]

In B1 on the cc sheet enter & copy down:

=INDEX(val!$C$1:$C$8,MATCH(A1&CHAR(127)&2,val!$D$1:$D$8,0))

 

[Aladin Akyurek]

If I were you, I'd do the following:

In C1 on the cc sheet enter & copy down:

=MATCH(A1&CHAR(127)&2,val!$D$1:$D$8,0)

In B1 on the cc sheet enter & copy down:

=IF(ISNUMBER(C1),INDEX(val!$C$1:$C$8,C1),"")

If you insist on a single formula in B1, which will be necessarily less
efficient, try:

=IF(ISNUMBER(MATCH(A1&CHAR(127)&2,val!$D$1:$D$8,0)),INDEX(val!$C$1:$C$8,MATC
H(A1&CHAR(127)&2,val!$D$1:$D$8,0)),"")

Thank you gentlemen, both your answers give the result I am looking for.
There is one change needed for both answers though, where there is no value
to return#N/A is returned

Pat

[...]

What follows is the test workbook I am using:

val!
A1 = car B1 = 1 C1 = T1
A2 = lorry B2 = 1 C2 = T4
A3 = lorry B3 = 2 C3 = T2
A4 = motorbike B4 = 1 C4 = T1
A5 = motorbike B5 = 2 C5 = T2
A6 = tractor B6 = 1 C6 = T2
A7 = motorbike B7 = 3 C7 = T4
A8 = car B8 = 2 C8 = T3

In D1 on the Val sheet enter & copy down:

=A1&CHAR(127)&B1

cc!
A1 = car B1 = T3
A2 = lorry B2 = T2
A3 = motorbike B3 = T2
A4 = tractor B4 =

[...]

In B1 on the cc sheet enter & copy down:

=INDEX(val!$C$1:$C$8,MATCH(A1&CHAR(127)&2,val!$D$1:$D$8,0))

 

[RagDyeR]

Try this array formula to eliminate the #N/A! error:

=IF(ISNA(MATCH(A1&2,val!A1:A8&val!B1:B8,0)),"",INDEX(val!$C$1:$C$8,MATCH(1,(
val!$A$1:$A$8=A1)*(val!$B$1:$B$8=2),0)))

Remember to enter with CSE.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


Thank you gentlemen, both your answers give the result I am looking for.
There is one change needed for both answers though, where there is no value
to return#N/A is returned

Pat

[...]

What follows is the test workbook I am using:

val!
A1 = car B1 = 1 C1 = T1
A2 = lorry B2 = 1 C2 = T4
A3 = lorry B3 = 2 C3 = T2
A4 = motorbike B4 = 1 C4 = T1
A5 = motorbike B5 = 2 C5 = T2
A6 = tractor B6 = 1 C6 = T2
A7 = motorbike B7 = 3 C7 = T4
A8 = car B8 = 2 C8 = T3

In D1 on the Val sheet enter & copy down:

=A1&CHAR(127)&B1

cc!
A1 = car B1 = T3
A2 = lorry B2 = T2
A3 = motorbike B3 = T2
A4 = tractor B4 =

[...]

In B1 on the cc sheet enter & copy down:

=INDEX(val!$C$1:$C$8,MATCH(A1&CHAR(127)&2,val!$D$1:$D$8,0))

 

[RagDyer]

Forgot to add absolutes to the error trap portion.
=IF(ISNA(MATCH(A1&2,val!$A$1:$A$20&val!$B$1:$B$20,0)),"",INDEX(val!$C$1:$C$2
0,MATCH(1,(val!$A$1:$A$20=A1)*(val!$B$1:$B$20=2),0)))
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


Try this array formula to eliminate the #N/A! error:

=IF(ISNA(MATCH(A1&2,val!A1:A8&val!B1:B8,0)),"",INDEX(val!$C$1:$C$8,MATCH(1,(
val!$A$1:$A$8=A1)*(val!$B$1:$B$8=2),0)))

Remember to enter with CSE.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


Thank you gentlemen, both your answers give the result I am looking for.
There is one change needed for both answers though, where there is no value
to return#N/A is returned

Pat

[...]

What follows is the test workbook I am using:

val!
A1 = car B1 = 1 C1 = T1
A2 = lorry B2 = 1 C2 = T4
A3 = lorry B3 = 2 C3 = T2
A4 = motorbike B4 = 1 C4 = T1
A5 = motorbike B5 = 2 C5 = T2
A6 = tractor B6 = 1 C6 = T2
A7 = motorbike B7 = 3 C7 = T4
A8 = car B8 = 2 C8 = T3

In D1 on the Val sheet enter & copy down:

=A1&CHAR(127)&B1

cc!
A1 = car B1 = T3
A2 = lorry B2 = T2
A3 = motorbike B3 = T2
A4 = tractor B4 =

[...]

In B1 on the cc sheet enter & copy down:

=INDEX(val!$C$1:$C$8,MATCH(A1&CHAR(127)&2,val!$D$1:$D$8,0))