Hello,
I have data arrange as this,
b2 c2 D2
1 2 3 ........17
B3 C3 D3
0 2 5 ..........15
Using the above criteria i would like to match C2 in col E then
corresponding C3 with col B and if number in Col B is below that of C3 then
return ''out' if over ''on target''

Hope this clear, I have tried this with an IF function but can't get it to
work?

Any help would be appreciated.

Thanks

Unclear. You are matching C2 in col E? and C3 in col B? What are you matching
exactly? You have no reference to what is in column E, and column B has 2
values that you show. More detail is needed. And perhaps what a desired
output would be for a given sample table.
--
John C


"Haz" wrote:

Hello,
I have data arrange as this,
b2 c2 D2
1 2 3 ........17
B3 C3 D3
0 2 5 ..........15
Using the above criteria i would like to match C2 in col E then
corresponding C3 with col B and if number in Col B is below that of C3 then
return ''out' if over ''on target''

Hope this clear, I have tried this with an IF function but can't get it to
work?

Any help would be appreciated.

Thanks

Hi thanks for responding,

Sorry, i''m trying to do two matches
Yes, i would like to match any number in order from 0 -17 that appear in a
row from b2 -S2 with the same numbers that appear randomly in Col E and when
they match then do a second match based on the set of numbers (0 -15) that
sit below in b3-S2 against numbers in Col B which are anything between 0 -15.
The result in col F would be a exact match which fine but if it is below say
'out' if it is higher say 'on target'

hope this is clearer but i can email you a table if you provide an email
address.
Thanks again

Haz



b2 c2 D2
1 2 3 ........17
B3 C3 D3
0 2 5 ..........15


Col B

"John C" wrote:

Unclear. You are matching C2 in col E? and C3 in col B? What are you matching
exactly? You have no reference to what is in column E, and column B has 2
values that you show. More detail is needed. And perhaps what a desired
output would be for a given sample table.
--
John C


"Haz" wrote:

Hello,
I have data arrange as this,
b2 c2 D2
1 2 3 ........17
B3 C3 D3
0 2 5 ..........15
Using the above criteria i would like to match C2 in col E then
corresponding C3 with col B and if number in Col B is below that of C3 then
return ''out' if over ''on target''

Hope this clear, I have tried this with an IF function but can't get it to
work?

Any help would be appreciated.

Thanks

I am still not able to wrap my mind around your question. I think because you
keep referring to columns when maybe you mean rows. Here is How I envision
your table
In cells range of B2:S2, and I assume these are static (always the same)
values:
0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | 13 | 14 | 15 | 16 | 17
In cells range of B3:S3 is a formula of some sort that will cause all these
cells to have the values between 0 and 15 (inclusive?)

And if I read your question right, you are basically want to know when,
given a certain number of 0-17, if it is greater than, less than, or equal to
than the value it sits directly above in row 3. (Column F has 2 values
already in it, 4 in F2, and randomnumber in F3, so don't know what you mean
by result in column F, unless it is like in cell F5. Word of advice, if you
are giving a result that is in a specific cell, give the specific cell, if a
range of cells, at least give the range start, such as F5:F20 or something).
Based on what I 'think' you are looking for, you could try this:

Assuming E5 is where your number of 0-17 is located, then:
F5: =IF(E5HLOOKUP(E5,$B$2:$S$3,2,FALSE),"on
target",IF(E5<HLOOKUP(E5,$B$2:$S$3,2,FALSE),"out", "equal"))

If they are equal, not sure how you want that handled. But could modify the
formula if it is = to be on target like so:
=IF(E5=HLOOKUP(E5,$B$2:$S$3,2,FALSE),"on target","out")
or if it is only if it is to be on target, and if it is <= then out, like
this:
=IF(E5HLOOKUP(E5,$B$2:$S$3,2,FALSE),"on target","out")


--
John C


"Haz" wrote:

Hi thanks for responding,

Sorry, i''m trying to do two matches
Yes, i would like to match any number in order from 0 -17 that appear in a
row from b2 -S2 with the same numbers that appear randomly in Col E and when
they match then do a second match based on the set of numbers (0 -15) that
sit below in b3-S2 against numbers in Col B which are anything between 0 -15.
The result in col F would be a exact match which fine but if it is below say
'out' if it is higher say 'on target'

hope this is clearer but i can email you a table if you provide an email
address.
Thanks again

Haz



b2 c2 D2
1 2 3 ........17
B3 C3 D3
0 2 5 ..........15


Col B

"John C" wrote:

Unclear. You are matching C2 in col E? and C3 in col B? What are you matching
exactly? You have no reference to what is in column E, and column B has 2
values that you show. More detail is needed. And perhaps what a desired
output would be for a given sample table.
--
John C


"Haz" wrote:

Hello,
I have data arrange as this,
b2 c2 D2
1 2 3 ........17
B3 C3 D3
0 2 5 ..........15
Using the above criteria i would like to match C2 in col E then
corresponding C3 with col B and if number in Col B is below that of C3 then
return ''out' if over ''on target''

Hope this clear, I have tried this with an IF function but can't get it to
work?

Any help would be appreciated.

Thanks

This works fine, sorry wasn't clear first time round.

"John C" wrote:

I am still not able to wrap my mind around your question. I think because you
keep referring to columns when maybe you mean rows. Here is How I envision
your table
In cells range of B2:S2, and I assume these are static (always the same)
values:
0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | 13 | 14 | 15 | 16 | 17
In cells range of B3:S3 is a formula of some sort that will cause all these
cells to have the values between 0 and 15 (inclusive?)

And if I read your question right, you are basically want to know when,
given a certain number of 0-17, if it is greater than, less than, or equal to
than the value it sits directly above in row 3. (Column F has 2 values
already in it, 4 in F2, and randomnumber in F3, so don't know what you mean
by result in column F, unless it is like in cell F5. Word of advice, if you
are giving a result that is in a specific cell, give the specific cell, if a
range of cells, at least give the range start, such as F5:F20 or something).
Based on what I 'think' you are looking for, you could try this:

Assuming E5 is where your number of 0-17 is located, then:
F5: =IF(E5HLOOKUP(E5,$B$2:$S$3,2,FALSE),"on
target",IF(E5<HLOOKUP(E5,$B$2:$S$3,2,FALSE),"out", "equal"))

If they are equal, not sure how you want that handled. But could modify the
formula if it is = to be on target like so:
=IF(E5=HLOOKUP(E5,$B$2:$S$3,2,FALSE),"on target","out")
or if it is only if it is to be on target, and if it is <= then out, like
this:
=IF(E5HLOOKUP(E5,$B$2:$S$3,2,FALSE),"on target","out")


--
John C


"Haz" wrote:

Hi thanks for responding,

Sorry, i''m trying to do two matches
Yes, i would like to match any number in order from 0 -17 that appear in a
row from b2 -S2 with the same numbers that appear randomly in Col E and when
they match then do a second match based on the set of numbers (0 -15) that
sit below in b3-S2 against numbers in Col B which are anything between 0 -15.
The result in col F would be a exact match which fine but if it is below say
'out' if it is higher say 'on target'

hope this is clearer but i can email you a table if you provide an email
address.
Thanks again

Haz



b2 c2 D2
1 2 3 ........17
B3 C3 D3
0 2 5 ..........15


Col B

"John C" wrote:

Unclear. You are matching C2 in col E? and C3 in col B? What are you matching
exactly? You have no reference to what is in column E, and column B has 2
values that you show. More detail is needed. And perhaps what a desired
output would be for a given sample table.
--
John C


"Haz" wrote:

Hello,
I have data arrange as this,
b2 c2 D2
1 2 3 ........17
B3 C3 D3
0 2 5 ..........15
Using the above criteria i would like to match C2 in col E then
corresponding C3 with col B and if number in Col B is below that of C3 then
return ''out' if over ''on target''

Hope this clear, I have tried this with an IF function but can't get it to
work?

Any help would be appreciated.

Thanks