How to LOOKUP against a range of values

[sudipballa]

Hello Folks,

I have two columns (From and To) and i have a set of ranges in those.

Eg: I have records of Account Ranges.

I have one more column, where i have a account number. I want to do a look up against the above two columns having the range and find out if it is present in any of those or not.

Attached is a sample file.

THanks
Sb

 

[tomas]

what should be the output in present column?

 

[sudipballa]

what should be the output in present column?

Output should be just a flag like - Yes or Y wherever a match is found. Anything as long as i am able to know if it is present in that ranges

 

[azumi]

Please find the file attach, hope thhis works

 

[sudipballa]

Azumi - Thanks a lot for the file. Will you be able to throw some light on what you tried to do inside the formula? I tried going through it, but didnt get it.

 

[Khalid NGO]

Hi Sb,
May be a non-array...
in H2:
=SUMPRODUCT(($A$2:$A$8<=G2)*($B$2:$B$8>=G2)*($A$2:$A$8))&" - "&SUMPRODUCT(($A$2:$A$8<=G2)*($B$2:$B$8>=G2)*($B$2:$B$8))

Copy down till H4.

Regards,

 

[Khalid NGO]

or with SUMIFS:

=SUMIFS($A$2:$A$8,$A$2:$A$8,"<="&G2,$B$2:$B$8,">="&G2)&" - "&SUMIFS($B$2:$B$8,$A$2:$A$8,"<="&G2,$B$2:$B$8,">="&G2)

 

[sudipballa]

Thanks a lot Khalid