# 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:AA1I have a list of names from A2:A30I need to be able to lookup a specific date and a specific name to return a valueEG 25/5/13 = G1 Tony Gibb = A15Result = the value in G15How do I go about this, I think I am over thinking the answer

See More: LOOKUP data across and down

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) + 1will 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.

#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? MIKEmessage 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 intersectionof 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.MIKEmessage 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.

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. :-)

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. :-)MIKEhttp://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 forColumn D to I has subsequent info in the cellsAny 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 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)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 AFTo get the info in the next column over, IEcolumn 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 : AF6We 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.MIKEmessage edited by mmcconaghy

Report •

#10
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) + 1will 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.

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 guysThanks againTony

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 : QW51Your 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 soD=1,E=2,F=3,G=4,H=5,I=6,J=7,K=8plus 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)MIKEhttp://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.MIKEhttp://www.skeptic.com/

Report •