Find Matching Data Within Numerous Rows
Ok, got it!
Assuming your data starts on row 2...
Enter this array formula** in B2:
=INDEX(E$2:E$7,MATCH(1,(A2=C$2:C$7)*(A2<=D$2:D$7) ,0))
Copy down as needed
** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)
--
Biff
Microsoft Excel MVP
"DPingger" wrote in message
...
Thanks, TVal.
I've lost sleep for two days now thinking about this.
Here it is.
Col A
3/15/2008
2/28/2008
6/1/2008
5/15/2008
Col C D E
1/1/2008 1/29/2008 Jan 2008
1/30/2008 2/26/2008 Feb 2008
2/27/2008 3/28/2008 Mar 2008
3/29/2008 4/27/2008 Apr 2008
4/28/2008 5/24/2008 May 2008
5/25/2008 6/29/2008 Jun 2008
Desired result after comparison to col C:D:
Col A B
3/15/2008 Mar 2008
2/28/2008 Mar 2008
6/1/2008 Jun 2008
5/15/2008 May 2008
There are 4 thousand date entries in column A.
"T. Valko" wrote:
Not sure I follow you on this.
Can you post a small example of your data and demonstrate what result you
want?
--
Biff
Microsoft Excel MVP
"DPingger" wrote in message
...
TVal.
I have a similar problem comparing dates.
A1:A20 has the dates that needs to be compared to a range of dates in
C1:D25
and if within range then enter the value in E1:E25 in corresponding
Cell
in
column B.
I've tried a simple If statement to no avail.
=If(C1<A1<D1,E1,if(C2<A1<D2,E2........ not working.
Help!!!
TIA
Pingger
"T. Valko" wrote:
I've tried vlookup, but I'm not getting the results I was looking
for.
How are we supposed to know what result you're looking for if you
don't
tell
us!
Need more detail.
For example:
I want to find the value in A1 that is somewhere in the range L1:L1000
and
when found return the corresponding value from the range M1:M1000. The
data
to be returned is _____. (text, numeric, could be either)
--
Biff
Microsoft Excel MVP
"SMH" wrote in message
...
There are two sets of data with one field matching in both sets. I
want
to
be able to put a formula into a cell and find the matching field,
whether
it
is in cell 10 or cell 1000. I've tried vlookup, but I'm not getting
the
results I was looking for. Any other suggestions.
|