Array formula does not return consistent result

Hi knowleqe. Welcome to the forum.

After concatenation with empty cells "Barley Flats01" does indeed match at row 1426. MATCH returns 1424. This is relative to row 3 (the first row of the range(s). Factoring for the ranges starting at row 3 (Names!$BE$3:$BE$7514) is absolute row 1426.

Type BE1426 in the name drop down box and hit enter. There is a 1 there.

Am I missing something?

yes, Barley Flats matches in 'Names!'G1426 but not in 'Names!'H1426.

Contatenating 'Names!'H1426&'Names!'G1426&'Names!'BB1426&'Names!'BF1426 returns "BARLEY FLATS01" the same as if there were no 'Names!'H1426.

I've re uploaded the file. I've hidden all but the relevant columns in 'Names' to make it easier to follow what I mean. Row 1426 is highlighted yellow.

If this is not what you are expecting perhaps you need additional criteria in your formula? Since I don't know the overall objectives I really can't advise on that.

Let me know if this helps.

It appears that in column W Barley Flats et. al. have already been tested against column H. That is why they return "blanks". They aren't there.

Overall it looks to me like your formula is working properly.

Otherwise keep looking at the next parameters. That's why I think the array is inconsistent since "Abbey" is found in 'Names!'H:H and returns "1" but "Whitworth" and "Whizzin" return "blank" since they are not in 'Names!'H:H.

Yes, and "Whitworth01" and "Whizzin01" won't match because "Whitworth00" and "Whizzin00" are the results of the concatenation in Names!$H$3:$H$7514&Names!$G$3:$G$7514&Names!$BB$3:$BB$7514&Names!$BF$3:$BF$7514. It begins to appear that the problems may lie in column BF. Is there any chance some of the 1s and 0s are typos in that column?

knowleqe welcome to the forum

Don't quote whole posts -- it's just clutter. If you are responding to a post out of sequence, limit quoted content to a few relevant lines that makes clear to whom and what you are responding

For normal conversational replies, try using the QUICK REPLY box below.

but I thought the array should test the first result then the first ampersand then the second and so on.

No. There are no tests in your formula. Tests require functions / operators that test TRUE / FALSE. What your formula does is instruct Excel to concatenate a string build unconditionally. Then it instructs Excel to MATCH that to an array of other concatenated strings (built unconditionally).

If the intent is to concatenate conditionally that requires a different formula.

Also the only match for Barley Flats that can return a 0 is Barley FlatsVERDUGO01. (row 2379) Even if all the tests you list were imposed I am still puzzled by the expectation of a 0 return.

Having said all that I will hazard a guess. Try entering this array formula in Y4 of 'Analysis'.

Formula:

Please Login or Register  to view this content.

This coerces a 0 if there are no matches in H:H.

I thought the first match value is measured against the first range, the second match value the second range, and so forth? That's why I keep going back to "Barley Flats" is not found in Names!$H3:H514. Is that correct?

No. It doesn't work that way.

There is no order of matching as you describe. It just builds strings. MATCH treats those exactly the same as if "Barley Flats01" and others were in single cells.

What do you mean by unconditionally? Is that to say that "Barley Flats01" is the same as "0Barley Flats1"?

No

When I say unconditionally I mean there is no "IF(condition,do this,else do this other thing)" implied in concatenation. Concatenating only instructs Excel to "do this" (build the string) without qualification (unconditionally).

In order to do what you're understanding is ("first match value against first range ....") you would need to insert some kind of IF condition(s) into your formula. In other words build the strings (concatenate) conditionally.

That's where I get confused. Even if you were to define conditions I see no where to go with them in the INDEX/MATCH because the returns you expect are not possible with the data you have. There are no 0s in BE that meet requirements.

As I understand you in order to return 0s they need to be coerced. That is why I suggested the formula I did. It is all I could think of if we were "talking past on another" as you say.

Did you try the formula I suggested? It produces the output you describe, but not in the way the rest of your formula and comments suggest you want it to do.

Since 'Barley Flats' is the first value the concatenation would be "Barley Flats01" but that's not an option in Names!$H$3:$H$7514&Names!$G$3:$G$7514&Names!$BB$3:$BB$7514&Names!$BF$3:$BF$7514.

Yes it is. Try filtering Barley Flats in 'Names'

(I concatenated each H:H&G:G&B:B&BF:BF in column BG to illustrate.)

Row\Col	G	H	BB	BC	BD	BE	BF	BG
826	BARLEY FLATS		0	1	1	1	0	BARLEY FLATS00
1425	BARLEY FLATS		0	1	0	1	0	BARLEY FLATS00
1426	BARLEY FLATS		0	0	1	1	1	BARLEY FLATS01
1427	BARLEY FLATS	RAVINE	0	0	0	1	1	RAVINEBARLEY FLATS01
2379	BARLEY FLATS	VERDUGO	0	0	0	0	1	VERDUGOBARLEY FLATS01