"Lookup" formula for a range of values (instead of a value)

**thisssisme** · 04-25-2014, 12:55 PM

I have a large amount of data. In column A is the time, in column B is the data.

I would like to display in a new location when the data is in between 2 values. So I don't want to highlight the data that falls into this desired range (because then I still have to look through the data). I want to paste the time stamp corresponding to each time the data falls into this desired range.

Example:
If the desired range is 10<x<20, then I'd like to post in ColC each time stamp from ColA when the data from ColB falls in this range:

A - B - C
1 - 5 - 3
2 - 5 - 5
3 - 12
4 - 30
5 - 14

Any Help?

**Bernie Deitrick** · 04-25-2014, 02:06 PM

Array enter this in C1 (use Ctrl-Shift-Enter instead of just enter)
=IF(COUNTIFS(B:B,">=10",B:B,"<=20")>=ROW(A1),INDEX(A:A,SMALL(IF($B$1:$B$1000>=10,IF($B$1:$B$1000<=20,ROW($B$1:$B$1000))),ROW(A1))),"")
then copy down until it returns blanks.

**thisssisme** · 04-25-2014, 02:39 PM

Perfect thanks for the quick help! Much appreciated!

I'll modify to fit my needs now and I'm good to go.

"Lookup" formula for a range of values (instead of a value)

LinkBack

Thread Tools

Rate This Thread

Display

"Lookup" formula for a range of values (instead of a value)

Re: "Lookup" formula for a range of values (instead of a value)

Re: "Lookup" formula for a range of values (instead of a value)

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] I want Range("A1:K1") values divide to "4" in the Range("L1:U1") cells.

display unique values of a data range "n" columns by "n" rows

[SOLVED] Find "X" in range of cells and return various values based on the column "X" is found in.

[SOLVED] Formula needed to display "Pass" or "Fail" if a column contains any values other than "yes

[SOLVED] Pasting values between ranges in different workbooks: "Range" works but "Cells" doesn't

Bookmarks

Bookmarks

Posting Permissions