Solved LOOKUP data across and down

September 13, 2013 at 09:29:23
Specs: Windows 7
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:A30

I need to be able to lookup a specific date and a specific name to return a value

EG 25/5/13 = G1
Tony Gibb = A15

Result = the value in G15

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


See More: LOOKUP data across and down

Report •

✔ Best Answer
September 13, 2013 at 11:54:47
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             Bill
 

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



#1
September 13, 2013 at 09:59:52
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

http://www.skeptic.com/

message edited by mmcconaghy


Report •

#2
September 13, 2013 at 10:14:56
Not done that before, so a bit of instruction would help


Report •

#3
September 13, 2013 at 10:17:24
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

http://www.skeptic.com/

message edited by mmcconaghy


Report •

Related Solutions

#4
September 13, 2013 at 10:28:10
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.


Report •

#5
September 13, 2013 at 10:43:55
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_array used 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 postion within an array and the INDEX function needs a row and column number within 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 when all of 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.


Report •

#6
September 13, 2013 at 10:57:42
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

http://www.skeptic.com/


Report •

#7
September 13, 2013 at 11:14:11
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 cells

Any ideas


Report •

#8
September 13, 2013 at 11:36:28
re: My only problem being is that the dates are every 10 cells, so 9 blanks

I 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 for

Carefully 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


Report •

#9
September 13, 2013 at 11:50:17
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 AF

To 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)+1

See how that works.

MIKE

http://www.skeptic.com/

message edited by mmcconaghy


Report •

#10
September 13, 2013 at 11:54:47
✔ 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             Bill
 

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


Report •

#11
September 13, 2013 at 11:54:51
=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


Report •

#12
September 13, 2013 at 12:23:40
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 guys

Thanks again
Tony


Report •

#13
September 13, 2013 at 12:35:18
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 the lookup_array used 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

http://www.skeptic.com/


Report •

#14
September 13, 2013 at 13:01:12
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

http://www.skeptic.com/


Report •

Ask Question