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

Report •

✔ Best Answer
December 2, 2016 at 12:14:24
Using 50 REFERENCE AREAs, might make the formula a bit long, but
you can use a cell reference to call up what ever area you choose, so
I 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)

MIKE

http://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............

MIKE

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

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


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 obeyed
Hopefully I've shown this correctly
So 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 data

The 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 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,

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.

MIKE

http://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.

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

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 better
The problem I have is as follows

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,

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 S5

You can't expect MATCH(B8,A5:F5,0) to find any values beyond F5 so nothing other than Date1 will ever be found.

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


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:F5

Your 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 1
it 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))

MIKE

http://www.skeptic.com/

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

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

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:O5

The 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 5
within the REFERENCE AREA regardless of what they are on the sheet.

It can get very confusing, very fast.


MIKE

http://www.skeptic.com/

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

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


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 formula
I have 50 reference areas, so a bit of manipulation I can attain the result I need

It 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 identical

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

MIKE

http://www.skeptic.com/


Report •

#14
December 2, 2016 at 12:14:24
✔ Best Answer
Using 50 REFERENCE AREAs, might make the formula a bit long, but
you can use a cell reference to call up what ever area you choose, so
I 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)

MIKE

http://www.skeptic.com/


Report •

#15
December 2, 2016 at 13:43:17
Here is a way to shorten the formula and make your life a bit easier.

First you need to use the DEFINE NAME function.

Name your 50 areas like:

area1 = A3:F5
area2 = I3:O5
area3 = Q3:W5
area4 = Y3:AE5
etc, etc.

Note the Number following the word "area".

Once you have all 50 areas named, modify the formula like:

=INDEX(INDIRECT("area"&A10),MATCH(A8,A3:A5,0),MATCH(A9,A2:F2,0))
=INDEX(INDIRECT("area"&A10),MATCH(A8,A3:A5,0),MATCH(A9,A2:G2,0))

Now the formula uses your Defined Names as the Reference Area array,
with cell A10 determining which area to check.

Since we now only use one area, the last number in the formula will always be 1 and since the default is 1, we can eliminate it from the formula.

With 50 areas to check, it might be a good idea to actually note on the spreadsheet which areas are associated with each number.

See how that works for you.

MIKE

http://www.skeptic.com/

message edited by mmcconaghy


Report •

Ask Question