Find matching value

[Maheshp]

I have dates in rows from (A1:D1) & have dates in column (G1:G5) i want User define function or formula which will reflect matching value from both ranges in E1 cell.

Rows
<TABLE style="WIDTH: 205pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=273><COLGROUP><COL style="WIDTH: 50pt; mso-width-source: userset; mso-width-alt: 2450" width=67><COL style="WIDTH: 52pt; mso-width-source: userset; mso-width-alt: 2523" span=2 width=69><COL style="WIDTH: 51pt; mso-width-source: userset; mso-width-alt: 2486" width=68><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 50pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20 width=67 align=right>01-Apr-10</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 52pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=69 align=right>31-Mar-11</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 52pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=69 align=right>01-Mar-11</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 51pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=68 align=right>28-Feb-12</TD></TR></TBODY></TABLE>

column
<TABLE style="WIDTH: 53pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=70><COLGROUP><COL style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 2560" width=70><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 53pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20 width=70 align=right>31-Jul-11</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20 align=right>31-Aug-11</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20 align=right>31-Mar-11</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20 align=right>31-Oct-11</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20 align=right>30-Nov-11</TD></TR></TBODY></TABLE>

results in cell E1 should be <TABLE style="WIDTH: 52pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=69><COLGROUP><COL style="WIDTH: 52pt; mso-width-source: userset; mso-width-alt: 2523" width=69><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 52pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20 width=69 align=right>31-Mar-11</TD></TR></TBODY></TABLE>

 

[Aladin Akyurek]

I have dates in rows from (A1:D1) & have dates in column (G1:G5) i want User define function or formula which will reflect matching value from both ranges in E1 cell.

Rows
<TABLE style="WIDTH: 205pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=273><COLGROUP><COL style="WIDTH: 50pt; mso-width-source: userset; mso-width-alt: 2450" width=67><COL style="WIDTH: 52pt; mso-width-source: userset; mso-width-alt: 2523" span=2 width=69><COL style="WIDTH: 51pt; mso-width-source: userset; mso-width-alt: 2486" width=68><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 50pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20 width=67 align=right>01-Apr-10</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 52pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=69 align=right>31-Mar-11</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 52pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=69 align=right>01-Mar-11</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 51pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=68 align=right>28-Feb-12</TD></TR></TBODY></TABLE>

column
<TABLE style="WIDTH: 53pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=70><COLGROUP><COL style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 2560" width=70><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 53pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20 width=70 align=right>31-Jul-11</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20 align=right>31-Aug-11</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20 align=right>31-Mar-11</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20 align=right>31-Oct-11</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20 align=right>30-Nov-11</TD></TR></TBODY></TABLE>

results in cell E1 should be <TABLE style="WIDTH: 52pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=69><COLGROUP><COL style="WIDTH: 52pt; mso-width-source: userset; mso-width-alt: 2523" width=69><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 52pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20 width=69 align=right>31-Mar-11</TD></TR></TBODY></TABLE>

Assuming a single match or none...

E1:

=INDEX(G1:G5,LOOKUP(9.99999999999999E+307,MATCH(A1:D1,G1:G5,0)))

 

[Ron Coderre]

Using your posted example,
this regular formula, formatted as a date, returns the largest date that occurs in both lists:

E1: =MAX(INDEX(COUNTIF(A1:D1,G1:G5)*G1:G5,0))
if there may be duplicate dates in A1:D1 that happen to match a G1:G5 date,
this regular formula accommodates that:
=MAX(INDEX((COUNTIF(A1:D1,G1:G5)>0)*G1:G5,0))

Note: if there are no matching dates, the formula returns a zero.

Is that something you can work with?

 

[Maheshp]

Thanks Ron & Aladin for your help and time
but what's the meaning of this part 9.99999999999999E+307 in formula ?

 

[T. Valko]

Thanks Ron & Aladin for your help and time
but what's the meaning of this part 9.99999999999999E+307 in formula ?

Here's the simplified version:

=INDEX(G1:G5,LOOKUP(5,MATCH(A1:D1,G1:G5,0)))

Here's another one.

Array entered**:

=MAX(IF(G1:G5=A1:D1,G1:G5))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Format as Date

 

[Ron Coderre]

Thanks Ron & Aladin for your help and time
but what's the meaning of this part 9.99999999999999E+307 in formula ?

If the search value in the LOOKUP function is larger than any of the lookup_range values, the function returns the last listed value of the same type (text or numeric) as the search value.

In the posted formula, the largest value the MATCH function would return would be 5 (there are only 5 items listed). Consequently, you could replace the 9.99999999999999E+307 (which is the largest number that Excel can handle) with any number greater than or equal to 5.
(I typically use 10^99, since the lookup_range has no chance of containing a value that large.)

I hope that helps.

 

[Aladin Akyurek]

Thanks Ron & Aladin for your help and time
but what's the meaning of this part 9.99999999999999E+307 in formula ?

You are welcome.

LOOKUP (also VLOOKUP, HLOOKUP, INDEX/MATCH with match-type set to 1) using this large value which is anchored in Excel itseld) as look up value will return last numeric value from the reference it's fed with.

In the formula I suggested LOOKUP fetches the last position value MATCH returns and feeds that position to INDEX that calculates the desired result.

See for more including an explanation of the underlying algorithm...

http://www.mrexcel.com/forum/showthread.php?t=102091 (post #3)

http://www.mrexcel.com/forum/showthread.php?t=310278 (post #7)