Hi Guys,

been struggling with this todayA 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

✔ 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: | xxThis 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

Tony, You've been posting here long enough to know how to use the < PRE > tags............

MIKE

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.

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

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

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~~rowargument 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

=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 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

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.

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 isreferringto 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

message edited by mmcconaghy

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 thisDate1,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:

5,0)XThat would explain why his =MATCH(A8,A5:

5,0) returns #N/A for anything other than Date1. He is not searching beyond Column F.FNow, 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

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 5

within the REFERENCE AREA regardless of what they are on the sheet.It can get very confusing, very fast.

MIKE

message edited by mmcconaghy

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.

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 needIt did look to me that what I was trying to do might not work. Appreciate your efforts on helping, Many thanks guys

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.

MIKE

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

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

message edited by mmcconaghy

Ask Your Question

Weekly Poll