Find Matching Data Within Numerous Rows
 

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.

Find Matching Data Within Numerous Rows
 

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.

Find Matching Data Within Numerous Rows
 

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" 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.

Find Matching Data Within Numerous Rows
 

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)


--
Biff
Microsoft Excel MVP


"SMH" wrote in message
...
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" 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.

Find Matching Data Within Numerous Rows
 

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.

Find Matching Data Within Numerous Rows
 

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.

Find Matching Data Within Numerous Rows
 

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.

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.

Find Matching Data Within Numerous Rows
 

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" wrote:

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.

Find Matching Data Within Numerous Rows
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"DPingger" wrote in message
...
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" wrote:

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.