Conditional Formula to search ranges??
 

I need to prepare a conditional formula that will confirm or reject the data
in Column A to the data in Columns B and C as well as the range of numbers in
between B and C. Columns B and C are roughly twenty thousand lines and range
anywhere from 1 to 1000 numbers. For example:


Column A Column B Column C

473190044 491987100 491987199
511373007 491987200 491987299
542418053 491987300 491988300

Can excel search a range of 100-199 like in columns B and C or will all
numbers require a individual cell?

I am not sure what you are trying to do, if you want to return what's in A
when a number is in-between 2 numbers in B and C you can use

=INDEX(A2:A20000,MATCH(1,(B2:B2000=491987200)*(C2 :C20000<=491987299),0))

entered with ctrl + shift & enter

you can "search" ranges

a filter would be another way to go


Best Regards,

Peo Sjoblom

"adean" wrote:

I need to prepare a conditional formula that will confirm or reject the data
in Column A to the data in Columns B and C as well as the range of numbers in
between B and C. Columns B and C are roughly twenty thousand lines and range
anywhere from 1 to 1000 numbers. For example:


Column A Column B Column C

473190044 491987100 491987199
511373007 491987200 491987299
542418053 491987300 491988300

Can excel search a range of 100-199 like in columns B and C or will all
numbers require a individual cell?

Thank you for your reply.

Will using the filter feature allow for the ranges??

My end result needs to be a yes or no (or colour change) if the number in
say A1 matches or falls within the ranges of B1 through C1, B2 through C2,
etc. I need it to search within all twenty thousand lines in columns B and
C. Then the formula to copy to cell A2 to search for a match starting at B1
and C1.

Also - can the formula be written using data from different sheets or does
all data have to be in the same sheet. I would rather list the 20 thousand
plus lines of number ranges on a seperate sheet if possible.

Angie

"Peo Sjoblom" wrote:

I am not sure what you are trying to do, if you want to return what's in A
when a number is in-between 2 numbers in B and C you can use

=INDEX(A2:A20000,MATCH(1,(B2:B2000=491987200)*(C2 :C20000<=491987299),0))

entered with ctrl + shift & enter

you can "search" ranges

a filter would be another way to go


Best Regards,

Peo Sjoblom

"adean" wrote:

I need to prepare a conditional formula that will confirm or reject the data
in Column A to the data in Columns B and C as well as the range of numbers in
between B and C. Columns B and C are roughly twenty thousand lines and range
anywhere from 1 to 1000 numbers. For example:


Column A Column B Column C

473190044 491987100 491987199
511373007 491987200 491987299
542418053 491987300 491988300

Can excel search a range of 100-199 like in columns B and C or will all
numbers require a individual cell?