compare betwen two values

  • Thread starter Thread starter PatLee
  • Start date Start date
P

PatLee

dear all,
in below table, when I input a number in the "input" cell, the result
will be returned the value of column C. I used vlookup function. But
this function cannot compare the number is between column A & B. In
below example, I expect it will return "Not Found". I tried to use IF,
but it was too long.
Please advise any other formula.

"=VLOOKUP(C8,B2:D6,3,TRUE)"

Col A Col B Col C
Row 1 1 4 A
Row 2 10 19 B
Row 3 20 29 C
Row 4 30 39 D
Row 5 40 50 E

Input => 9
Result : A

Thanks in advance,
Patrick.
 
This is with cell C8 being your input cell, and presumably cell B1 should
have read 9 and not 4

=CHAR(SUMPRODUCT((A1:A5<=C8)*(B1:B5>=C8)*(CODE(C1:C5))))

Hope this helps
 
Your formula should work, but may require amendment depending upon your
answer to these questions:

Are your columns actually B:D as used by your formula, or A:C as your
example shows?

In either case, with 9 in C8, do you want to return "Not Found", or "A"?
 
Your formula should work, but may require amendment depending upon your
answer to these questions:

Are your columns actually B:D as used by your formula, or A:C as your
example shows?

In either case, with 9 in C8, do you want to return "Not Found", or "A"?








- Show quoted text -
Click to expand...

Steve,
sorry for misleading
1) my formula should be :
=VLOOKUP(B8,A1:C5,3,TRUE)
2) with 9 in B8, I suppose it should return "Not Found" which don't
know how to define this.

Thanks,
Pat
 
Hi Pat,

If you can bear to have a result of #N/A (which Excel understands as Not
Avaliable) instead of "Not Found" (which Excel would only see as a string of
characters), just change the TRUE argument to FALSE. Otherwise:

=if(isna(VLOOKUP(B8,A1:C5,3,false)),
"Not Found",VLOOKUP(B8,A1:C5,3,false))



Your formula should work, but may require amendment depending upon your
answer to these questions:

Are your columns actually B:D as used by your formula, or A:C as your
example shows?

In either case, with 9 in C8, do you want to return "Not Found", or "A"?








- Show quoted text -
Click to expand...

Steve,
sorry for misleading
1) my formula should be :
=VLOOKUP(B8,A1:C5,3,TRUE)
2) with 9 in B8, I suppose it should return "Not Found" which don't
know how to define this.

Thanks,
Pat
 
Hi Pat,

If you can bear to have a result of #N/A (which Excel understands as Not
Avaliable) instead of "Not Found" (which Excel would only see as a stringof
characters), just change the TRUE argument to FALSE.  Otherwise:

=if(isna(VLOOKUP(B8,A1:C5,3,false)),
"Not Found",VLOOKUP(B8,A1:C5,3,false))








Steve,
sorry for misleading
1) my formula should be :
=VLOOKUP(B8,A1:C5,3,TRUE)
2) with 9 in B8, I suppose it should return "Not Found" which don't
know how to define this.

Thanks,
Pat- Hide quoted text -

- Show quoted text -
Click to expand...

Steve,
but by using your formula, if I put a 5, it will also show "Not found"
which I expect it returns the value"A".

Thanks
Pat
 
Hi Pat,

sorry, I misread your original question, try this instead:

=IF(ISNA(MATCH(1,INDEX((B8>=A1:A5)*(B8<=B1:B5),),0)),
"Not Found",INDEX(C1:C5,MATCH(1,INDEX((B8>=A1:A5)*
(B8<=B1:B5),),0)))




Hi Pat,

If you can bear to have a result of #N/A (which Excel understands as Not
Avaliable) instead of "Not Found" (which Excel would only see as a string
of
characters), just change the TRUE argument to FALSE. Otherwise:

=if(isna(VLOOKUP(B8,A1:C5,3,false)),
"Not Found",VLOOKUP(B8,A1:C5,3,false))








Steve,
sorry for misleading
1) my formula should be :
=VLOOKUP(B8,A1:C5,3,TRUE)
2) with 9 in B8, I suppose it should return "Not Found" which don't
know how to define this.

Thanks,
Pat- Hide quoted text -

- Show quoted text -
Click to expand...

Steve,
but by using your formula, if I put a 5, it will also show "Not found"
which I expect it returns the value"A".

Thanks
Pat
 
Hi Pat,

sorry, I misread your original question, try this instead:

=IF(ISNA(MATCH(1,INDEX((B8>=A1:A5)*(B8<=B1:B5),),0)),
"Not Found",INDEX(C1:C5,MATCH(1,INDEX((B8>=A1:A5)*
(B8<=B1:B5),),0)))











Steve,
but by using your formula, if I put a 5, it will also show "Not found"
which I expect it returns the value"A".

Thanks
Pat- Hide quoted text -

- Show quoted text -
Click to expand...

Steve,
it works, thanks.

dear all, thanks for all your help.

Pat.
 
Back
Top