Find Matching Data Within Numerous Rows

[SMH]

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.

 

[T. Valko]

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)

 

[SMH]

There are two worksheets, same workbook, with one unique field on both
sheets. Since the order of the data isn't consistant, I would like to write
a formula to find out if the unique # is found.

For example:
Data set 1
A1=abc
A100=def

Data set 2
A2=def
A6=abc

I want the formula to find the value of A1 (abc) in the second data set, in
this example in data set 2, A6.

The formula I am trying to use is =VLOOKUP(A2,Sheet2!B2:B50000,2,FALSE)

 

[T. Valko]

Ok, if you simply want to know if the values are present in both data sets:

=IF(COUNTIF(Sheet2!A$2:A$50000,A1),"Found","Not Found")

However, based on your posted lookup formula maybe this is what you want:

=VLOOKUP(A1,Sheet2!A$2:B$50000,2,0)

 

[DPingger]

TVal.

I have a similar problem comparing dates.

A1:A20 has the dates that needs to be compared to a range of dates in C125
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]

Not sure I follow you on this.

Can you post a small example of your data and demonstrate what result you
want?

 

[DPingger]

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:

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]

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$7),0))

Copy down as needed

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

 

[DPingger]

Mr T,

You're awesome!!!

Thanks a bunch; works like a charm. I will sleep better tonight. I just knew
there has to be a better solution to my "if" approach.

This community is the best!

DPingger

 

[T. Valko]

You're welcome. Thanks for the feedback!