# Solved Look up a value with INDEX MATCH

December 1, 2016 at 13:25:29
Specs: Windows 64
 Hi Guys,been struggling with this today A B C D E F G H I J K L M N O P Q 1 2 1 2 3 4 1 2 3 4 1 2 3 4 3 4 5 6 01/03/2016 5 3 4 6 06/06/2016 2 5 6 5 09/10/2016 6 5 2 4 7 8 9 10 06/06/2016 11 3 12 I've been trying to find the date A10, and the column number 3, to get the value '6'Stuck on this formula`=INDEX(A2,Q6,MATCH(A10,A2:A6,0),MATCH(A11,A2:Q2,0))`Where am I going wrong ????message edited by tonygibb

See More: Look up a value with INDEX MATCH

December 2, 2016 at 12:14:24
 Using 50 REFERENCE AREAs, might make the formula a bit long, butyou can use a cell reference to call up what ever area you choose, soI would say it's doable.So with your data like:``` A | B| C | D | E | F | G | H | I |J | K | L | M | N | O | 1) | | | | | | | | | | | | | | | 2) | | 1 | 2 | 3 | 4 | 5 | | | | 1 | 2 | 3 | 4 | 5 | 3) | | | | | | | | | | | | | | | 4) | | 11| 12| 13| 14| 15| | | | 11| 12| 13| 14| 15| 5) 15/05/16 | | 16| 17| 18| 19| 20| | 15/05/16 | | 16| 17| xx| 19| 20| 6) | | | | | | | | | | | | | | | 7) | | | | | | | | | | | | | | | 8) 15/05/16 | << Date 9) 3 | << Column Label 10) 2 | << Area To Check 1 - 50 11) Result: | xx ```This formula is for 4 areas. (I've only posted two) The two MATCH functions stay the same you just need to keep adding more reference areas within the parentheses.=INDEX((A3:F5,I3:O5,Q3:W5,Y3:AE5),MATCH(A8,A3:A5,0),MATCH(A9,A2:F2,0),A10)MIKEhttp://www.skeptic.com/

#1
December 1, 2016 at 13:29:28
 Tony,You've been posting here long enough to know how to use the < PRE > tags............MIKEhttp://www.skeptic.com/

Report •

#2
December 1, 2016 at 13:34:47
 Where does the INDEX function pull its results from? AFAIK, from an array.=INDEX(array, row_num, [column_num])Where's the reference to the array in your formula?(There may be other things wrong with your formula, but that one jumped right out at me.)

Report •

#3
December 2, 2016 at 06:13:27
 ``` A B C D E F 1) 1 2 3 4 5 2) 3) 4) 11 12 13 14 15 5) 15/05/16 16 17 18 19 20 6) 7) 8) Input Date: 15/05/16 9) Req : 3 10) 11) Result: 18 ````=INDEX(A3:F5,MATCH(A8,A5:F5,0),MATCH(A9,A2:F2,0))`Sorry about last nights post, I was getting hassle from her who should be obeyedHopefully I've shown this correctlySo where am I going wrong.???? I am trying to find the date in Row5, and column numbered '3'. This should return the value '18'I then have the same format set up every 10 columns across, but labelled with a different date and values, the headers'1,2,3,4,5'stay the same in each block of dataThe first block of data returns the value '18, however if I were to look for the 2nd date which would be in for example L5, the first MATCH in the formula returns N/A, so the problem is trying to find the second date.Row5 looks something like the followingDate1,Val,Val,Val,Val,Val, Date2,Val,Val,Val,Val,Val, Date3,Val,Val,Val,Val,Val, Date4,Val,Val,Val,Val,Val, message edited by tonygibb

Report •

Related Solutions

#4
December 2, 2016 at 07:52:23
 Haven't had a chance to really work on this, but I did notice that your second MATCH()MATCH(A9,A2:F2,0)Has the array using Row 2, but in your data example, Row 2 is empty.MIKEhttp://www.skeptic.com/

Report •

#5
December 2, 2016 at 08:06:44
 I am totally confused.You are trying to find matches for A8, which contains "Input Date:" and A9 which contains "Req :"Unless I am reading your table incorrectly, those values don't even exist in the array that you are trying to INDEX, A3:F5.In addition, you are trying to find a MATCH for the value in A9 in A2:F2, which appears to be an empty row. No match will ever be found, so a #N/A result is inevitable.Edit...never mind the following section, I was confused.Finally, the MATCH function that you are using for the INDEX function's row argument references a single Row (5). Therefore, any match it finds will return "1". Your INDEX function references A3:F5. Row 1 of that array (Excel Row 3) appears to be empty, so the only value the INDEX function could possibly return - if everything else was working - is 0.message edited by DerbyDad03

Report •

#6
December 2, 2016 at 08:25:05
 `=INDEX(A3:F5,MATCH(B8,A5:F5,0),MATCH(B9,A1:F1,0))`Sorry guys, a lot going on. The above should be correct, didn't want to send the actual formula as has references to another worksheet, have tried to simplify so I can understand your responses betterThe problem I have is as followsRow 5 looks something like this Date1,Val,Val,Val,Val,Val, Date2,Val,Val,Val,Val,Val, Date3,Val,Val,Val,Val,Val, Date4,Val,Val,Val,Val,Val, Date1 actually works and returns the right value, however if I change B8 to say Date3, that is when I get the #n/a, it is not locating the date along Row 5

Report •

#7
December 2, 2016 at 09:48:41
 re: Row5 looks something like the following Date1,Val,Val,Val,Val,Val, Date2,Val,Val,Val,Val,Val, Date3,Val,Val,Val,Val,Val, Date4,Val,Val,Val,Val,Val,That tells me that Row 5 contains data from A5:X5, based on the number of data elements you offered in your example.That puts Date2 in G5, Date3 in M5 and Date4 in S5You can't expect MATCH(B8,A5:F5,0) to find any values beyond F5 so nothing other than Date1 will ever be found.

Report •

#8
December 2, 2016 at 10:34:19
 This may take a bit of explaining.Your trying to use the Reference form of INDEX()The first argument is a Reference to an area: =INDEX(A3:F5Your second argument is for the ROW number within your Reference Area=INDEX(A3:F5,MATCH(A8,A5:F5,0)So this means that when MATCH(A8,A5:F5,0) evaluates out to the number 1it is referring to your REFERENCE AREA, which on your sheet is Row 3, IE A3:F5, but you are actually looking for row 5 on your sheet or Row 3 in your REFERENCE AREA.Confused? :-)What you want is: ```=INDEX(A3:F5, Row 3 of Ref Area A3:F5, Column 5 of Ref Area A3:F5, Area number to pick from 1 (You only have one area so this can be 0) ) ```Your first MATCH() should be something like: =MATCH(A8,A3:A5,0)Your whole formula, something like: =INDEX(A3:F5,MATCH(A8,A3:A5,0),MATCH(A9,A2:F2,0))MIKEmessage edited by mmcconaghy

Report •

#9
December 2, 2016 at 10:58:43
 re: Your first MATCH() should be something like: =MATCH(A8,A3:A5,0)Why is your lookup_array A3:A5? He said his Row 5 data look "something like this" and that he is trying to MATCH Date2, Date3, etc. but his formula only works for Date1:Row 5 looks something like this Date1,Val,Val,Val,Val,Val, Date2,Val,Val,Val,Val,Val, Date3,Val,Val,Val,Val,Val, Date4,Val,Val,Val,Val,Val,Seems to me that his first MATCH should be something like: =MATCH(A8,A5:X5,0)That would explain why his =MATCH(A8,A5:F5,0) returns #N/A for anything other than Date1. He is not searching beyond Column F.Now, I'll admit that his table, formulas and comments are rather confusing, so I may have this all wrong.message edited by DerbyDad03

Report •

#10
December 2, 2016 at 11:22:54
 so the problem is trying to find the second date.If you have two areas that are identical, then you will have to include the second REFERENCE AREA in the formula.With your duplicate area in the array I3:O5 your formula would be something like:=INDEX((A3:F5,I3:O5),MATCH(A8,A3:A5,0),MATCH(A9,A2:F2,0),2) Note at the end of the formula the number 2, this tells the INDEX formula to use the second REFERENCE Area, IE I3:O5The ROW & COLUMN numbers remain the same WITHIN THE REFERENCE AREA, even thou they many not be the same on the sheet.So the formula will always find ROW 3 and COLUMN 5within the REFERENCE AREA regardless of what they are on the sheet.It can get very confusing, very fast. MIKEmessage edited by mmcconaghy

Report •

#11
December 2, 2016 at 11:39:36
 I think what we have here is three people on three different pages.

Report •

#12
December 2, 2016 at 11:41:38
 Guys,Looking at Mike's last answer, what I require is not feasible with just one formulaI have 50 reference areas, so a bit of manipulation I can attain the result I needIt did look to me that what I was trying to do might not work. Appreciate your efforts on helping, Many thanks guys

Report •

#13
December 2, 2016 at 11:41:54
 Upon further thought,If you have two areas that are identicalThe areas need not be identical, but the formula will always use Row 3 and Column 5 of the REFERENCE Area.and the AREA number to choose, at the end of the formula, can be a Cell Reference.MIKEhttp://www.skeptic.com/

Report •

#14
December 2, 2016 at 12:14:24
 Using 50 REFERENCE AREAs, might make the formula a bit long, butyou can use a cell reference to call up what ever area you choose, soI would say it's doable.So with your data like:``` A | B| C | D | E | F | G | H | I |J | K | L | M | N | O | 1) | | | | | | | | | | | | | | | 2) | | 1 | 2 | 3 | 4 | 5 | | | | 1 | 2 | 3 | 4 | 5 | 3) | | | | | | | | | | | | | | | 4) | | 11| 12| 13| 14| 15| | | | 11| 12| 13| 14| 15| 5) 15/05/16 | | 16| 17| 18| 19| 20| | 15/05/16 | | 16| 17| xx| 19| 20| 6) | | | | | | | | | | | | | | | 7) | | | | | | | | | | | | | | | 8) 15/05/16 | << Date 9) 3 | << Column Label 10) 2 | << Area To Check 1 - 50 11) Result: | xx ```This formula is for 4 areas. (I've only posted two) The two MATCH functions stay the same you just need to keep adding more reference areas within the parentheses.=INDEX((A3:F5,I3:O5,Q3:W5,Y3:AE5),MATCH(A8,A3:A5,0),MATCH(A9,A2:F2,0),A10)MIKEhttp://www.skeptic.com/