Index Match.

[Taka83]

Hey all,

I was wondering if there was a way to get the match function to look over a grid of cells (A2:D7) rather than just a row or column?

I was looking to use a combination of Index and Match to look for a value inside a grid of cells (using match) and return another value (specifically a date) from the top row of the same column that the matched value was found.

Anyone know if this is possible using these functions, or alternatively another method of achieving this result?

Thanks in advance.

Tak

 

[GK039]

Hi Tak,

I am sure that a lot of people in this forum can help you with this but it would be better if you give us a sample of your data and the outcome you want

George

 

[Taka83]

What's the best way to upload a sample, or shall I just type it?

 

[T. Valko]

Hey all,

I was wondering if there was a way to get the match function to look over a grid of cells (A2:D7) rather than just a row or column?

I was looking to use a combination of Index and Match to look for a value inside a grid of cells (using match) and return another value (specifically a date) from the top row of the same column that the matched value was found.

Anyone know if this is possible using these functions, or alternatively another method of achieving this result?

Thanks in advance.

Tak

Maybe this...

Book1
	A	B	C	D	E
1	7/28/2012	4/15/2005	4/1/2011	1/12/2011	Lookup
2	6	9	17	14	1
3	24	15	22	10	4/1/2011
4	16	5	1	19	_
5	13	23	11	21	_
6	3	8	2	4	_
7	20	18	12	7	_
Sheet1

As long as the values are unique.

This array formula** entered in E3:

=INDEX(1:1,MAX(IF(A2:D7=E2,COLUMN(A2:D7))))

** 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

 

[Aladin Akyurek]

Hey all,

I was wondering if there was a way to get the match function to look over a grid of cells (A2:D7) rather than just a row or column?

I was looking to use a combination of Index and Match to look for a value inside a grid of cells (using match) and return another value (specifically a date) from the top row of the same column that the matched value was found.

Anyone know if this is possible using these functions, or alternatively another method of achieving this result?

Thanks in advance.

Tak

Given A1:D7, with dates in A1:D1:

<TABLE style="WIDTH: 197pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=263><COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><COL style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 2531" width=71><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19 width=64 align=right>4/1/2011</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64 align=right>4/8/2011</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 53pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=71 align=right>4/15/2011</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64 align=right>5/3/2011</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=19 align=right>24</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 align=right>5</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 align=right>40</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 align=right>22</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=19 align=right>10</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 align=right>0</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 align=right>2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 align=right>17</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=19 align=right>8</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 align=right>20</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 align=right>0</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 align=right>33</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=19 align=right>7</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 align=right>0</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 align=right>3</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 align=right>1</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=19 align=right>12</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 align=right>16</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 align=right>12</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 align=right>32</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=19 align=right>12</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 align=right>5</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 align=right>7</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 align=right>6</TD></TR></TBODY></TABLE>

and 12 as the value to look for, what would be the desired result(s)?

 

[Taka83]

Cheers gentlemen.

T.Valko has answered my query.

Could you elaborate on what the formula is saying, so I know for future reference.

Again many thanks to all who took the time to lend a hand!

 

[Taka83]

Also before I forget,

If the look up (E2 & E3) were on a totally different work book to the grid & dates, how would that affect the formula?

Cheers.

 

[T. Valko]

Cheers gentlemen.

T.Valko has answered my query.
Again many thanks to all who took the time to lend a hand!

You're welcome. Thanks for the feedback!

Could you elaborate on what the formula is saying, so I know for future reference.

=INDEX(1:1,MAX(IF(A2:D7=E2,COLUMN(A2:D7))))

INDEX(1:1

Is the location of the value that we want the formula to return.

MAX(IF(A2:D7=E2,COLUMN(A2:D7)))

The lookup value is in the range of cells A2:D7. We test that range to see which column the look value is in.

This expression will return an array of either TRUE or FALSE if the cells in the range contain the lookup value.

IF(A2:D7=E2

When the lookup value is found in a cell then that cells column number is passed to the MAX function. The MAX function then passes that column number to the INDEX function to tell it we want the value located in this column number.

Based on the sample I posted:

The lookup value is found in cell C4. C4 is column number 3.

So:

MAX(IF(A2:D7=E2,COLUMN(A2:D7))) = 3

INDEX(1:1,3) = C1, the 3rd column on row 1.

C1 = 4/1/2011

So:

=INDEX(1:1,MAX(IF(A2:D7=E2,COLUMN(A2:D7))))

= 4/1/2011

 

[Taka83]

Thanks for the crash course Valko.

What would I have to change in the formula if the look up (E2 & E3) were on a totally different work book to the grid & dates?

 

[T. Valko]

Also before I forget,

If the look up (E2 & E3) were on a totally different work book to the grid & dates, how would that affect the formula?

Cheers.

In a different FILE or on a different SHEET within the same file?

Just include the sheet/file reference.

The best way to do this is to start typing in the formula:

=INDEX(

And then use your mouse to select/point to the references used in the formula. That way Excel does all the work of entering the references.