Hi,

Another lookup poser, I have seen something in relation to this but can not remember where.I have a list of dates across the worksheet from B1:AA1

I have a list of names from A2:A30I need to be able to lookup a specific date and a specific name to return a value

EG 25/5/13 = G1

Tony Gibb = A15Result = the value in G15

How do I go about this, I think I am over thinking the answer

✔ Best Answer

Perhaps I should explain the MATCH function in a little more depth: Let's say you have this data:

A B C D E F G H I 1 Tom Fred Steve BillIf I use this formula I will get 5, because "Steve" is located in the 5th column of the array C1:I1

=MATCH("Steve", C1:I1, 0)

Since MATCH simply returns a number, I can perform a mathamatical operation on it.

=MATCH("Steve", C1:I1, 0) + 1

will return 6.

Therefore, if I use MATCH to determine the column_number for the INDEX function, I can "shift" the column that the INDEX function will use by simply modifying the value returned by MATCH.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

You could probably do it with a =SUMPRODUCT() or even a =COUNTIFS()

but don't have enough info.Post an example of how your data is set up.

Where is the input coming from?

MIKE

message edited by mmcconaghy

Not done that before, so a bit of instruction would help

If your data look like this: A B C D E F 1) 09/13/2013 09/14/2013 09/15/2013 09/16/2013 09/17/2013 2) John Smith 1 2 3 4 5 3) John Public 6 7 8 9 10 4) Tony Gibb 11 12 13 14 15 5) Jane Doe 16 17 18 19 20 6) Sally Smith 21 22 23 24 25 7) 8) Input Name: Tony Gibb 9) Input Date: 09/16/2013 10) 11) Result:In cell B11 enter the formula:

=INDEX(B2:F6,MATCH(B8,A2:A6,0),MATCH(B9,B1:F1,0))

Should return the number 14, which is the intersection

of Row 4 and column E.NOTE ADDED;

You might want to use a dropbox for the Input Name so the spelling is correct and you don't get error messages.

MIKE

message edited by mmcconaghy

You could combine INDEX and MATCH... INDEX( array, row_number, [column_number] )

MATCH( value, array, [match_type] )

To get this:

INDEX( array, MATCH( value, array, [match_type] ), MATCH( value, array, [match_type] ) )

e.g.

=INDEX(B2:AA30,MATCH(

your name,A2:A30,0),MATCH(your date,B1:AA1,0))Keep in mind that the MATCH function returns the position where the value was found within the array, which may not be the same as the actual Excel Row or Column.

For example, with a lookup_array of A2:A30, a value found in A2 will return 1, since A2 is the first position within the array A2:A9.

The INDEX/MATCH technique can also be used to "look left" or "look up" in a table. VLOOKUP and HLOOKUP only look Right and Down respectively, so you need another method in order to look left or up.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

re: " Should return the number 14, which is the intersection of Row 4 and column E."While "physically" correct, the value was actually found at the intersection of Row 3, Column 4 of the

lookup_arrayused by the INDEX function.I only bring that up to reduce the confusion users often have when using the MATCH and INDEX functions.

Since the MATCH function returns a

postionwithin an array and the INDEX function needs a row and column numberwithin it's own array, I like to always point out that those values may or may not be the same as the row or column number of the spreadsheet. Only whenallof the arrays start at A1 will the position numbers returned by MATCH - and used by INDEX - be the same as the Row and Column numbers of the spreadsheet.I still get confused by that every now and then and find myself counting rows and columns by tapping my finger on my monitor. :-)

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

the value was actually found at the intersection of Row 3, Column 4 of the lookup_array used by the INDEX function.A subtle point, but one well worth explaining.

counting rows and columns by tapping my finger on my monitor.Been there, done that, more than once. :-)

MIKE

Guys, thank you very much for your help on this.

My only problem being is that the dates are every 10 cells, so 9 blanks. Further to this, the info I need is then the next cell along, ie date is in column D, info required is in column E against the name the info is required for

Column D to I has subsequent info in the cellsAny ideas

re: My only problem being is that the dates are every 10 cells, so 9 blanksI don't see why that matters. MATCH is going to return the Column (or Row) number within the array that it is searching based on where it finds the lookup_value. You could have a single date in a range of 1000 otherwise blank cells and the MATCH function will return the Column (or Row) in which the match occurred.

re:

the info I need is then the next cell along, ie date is in column D, info required is in column E against the name the info is required forCarefully read Response # 4 and notice what the MATCH functions are used for. They return the row_number and column_number that the INDEX function will use.

If you know that the data you need is always one column to the right of the column found by the MATCH function, simply add 1 to it.

=INDEX(B2:AA30,MATCH(your name,A2:A30,0),MATCH(your date,B1:AA1,0)+1)

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03

The =INDEX(), =MATCH() does not care about blanks,

just extend your array to cover all the cells you need.IE;

Row 2 is the Date Row,

I extended it to column AF, with a Date every ten columns,

so I had dates in column B, L, V, and AFTo get the info in the next column over, IE

column C, or M, or W, or AB, just add one to the

MATCH function.Somthing like :

=INDEX(B2:AF6,MATCH(B8,A2:A6,0),MATCH(B9,B1:AF1,0)+1)

Note the INDEX array is now B2 : AF6

We still do a regular MATCH on the Name: MATCH(B8,A2:A6,0)

but for the data we add 1 so we get the next column: MATCH(B9,B1:AF1,0)+1See how that works.

MIKE

message edited by mmcconaghy

Perhaps I should explain the MATCH function in a little more depth: Let's say you have this data:

A B C D E F G H I 1 Tom Fred Steve BillIf I use this formula I will get 5, because "Steve" is located in the 5th column of the array C1:I1

=MATCH("Steve", C1:I1, 0)

Since MATCH simply returns a number, I can perform a mathamatical operation on it.

=MATCH("Steve", C1:I1, 0) + 1

will return 6.

Therefore, if I use MATCH to determine the column_number for the INDEX function, I can "shift" the column that the INDEX function will use by simply modifying the value returned by MATCH.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

=INDEX(Sheet8!C1:QW51,MATCH(F6,Sheet8!C4:C51,0),MATCH(F4,Sheet8!K1:QW1,0)+1) This is the formula I have but does not seem to fulfil my requirements

DerbyDad03 & Mike,

Many many thanks. After carefully reading your comments and a bit of formula manipulation, the result is perfect. Just what I require. This will know cut my input of data time in half.

Amazing what I am learning from you guysThanks again

Tony

Your offsetting everything, so you need to compensate.

Your INDEX array does not match the physical cell locations.Your array is C1 : QW51

Your first match doesn't start until row 4: MATCH(F6,Sheet8!C4:C51,0)

so you need to compensate by adding 3 to the match

so you get the correct physical row number, not the array row number.That's the point DERBYDAD03 was making with:

the value was actually found at the intersection of Row 3, Column 4 of thelookup_arrayused by the INDEX function.Same with the column match, your not starting until column K: MATCH(F4,Sheet3!K1:QW1,0)

so you need to add 8 to you get the correct column number,

your array starts at column C so

D=1,E=2,F=3,G=4,H=5,I=6,J=7,K=8

plus 1 to get the next column over,So your formula should look something like:

=INDEX(Sheet8!C1:QW51,MATCH(F6,Sheet8!C4:C51,0)+3,MATCH(F4,Sheet8!K1:QW1,0)+9)

MIKE

Alternatively you could forget about the offsets and just use the regular array numbers, =INDEX(Sheet8!C1:QW51,MATCH(F6,Sheet8!C1:C51,0),MATCH(F4,Sheet8!C1:QW1,0)+1)

Note everything starts at C1, unless there is possible conflict.

MIKE

Ask Your Question

Weekly Poll

Do you think Microsoft can save the Surface Book lineup?

Discuss in The Lounge

Poll History