(IF, INDIRECT, ARRAY) Search indirect ref (using IF) in an array

[grenvold]

I have two worksheets in my workbook. First one is full of data (headings in row 1, each row from 2 downwards is a new record); second one pulls out certain records based on specified criteria.

In Sheet2:
Because the column headings change (relative references are not an option in this case), I'm using MATCH to identify the column in which to search where $D$1 is the heading I want it to look for:
MATCH($D$1,Data!$1:$1,0) -----> Returns column of required heading.

And then adding to that the row
ROW() -----> Returns row that the formula is in - the row numbers on each worksheet are consistent i.e. I want the formulas in Sheet2! row 4 to search in Data! row 4.

So piecing them together I'm using
INDIRECT("Data!"&ADDRESS(ROW(),MATCH($D$1,QryAll!$1:$1,0)))
to return the contents of the same row in the searched column in the first tab.

This part works.

Then I wanted to add a conditional element and tried this:
{=IF(INDIRECT("Data!"&ADDRESS(ROW(),MATCH($D$1,Data!$1:$1,0)))=X15:X17,TRUE,FALSE)}
E.g. say the column I search for using the MATCH is 'vehicle type liked', and the INDIRECT above returns 'motorcycle'. I'm trying to then search in X15:X17 for if the person likes one of the vehicles in the "acceptable list" - either a motorcycle, van or truck (those three entered in X15:X17).
When I enter the formula above and enter as an array, I get #Value!.

If I modify the formula to refer directly to the cell instead of using INDIRECT, it works
e.g {=IF(Data!C3=X15:X17,TRUE,FALSE)}; with "motorcycle" in Data!C3, it returns "TRUE".

When I put in the INDIRECT (which when evaluated on its own returns "Data!C3"), it doesn't work.

It seems that the formula falls over in the use of the INDIRECT but not otherwise in the sequence/construction of the formula - how can I make the IF in the array accept the INDIRECT?

Regards and thanks in advance,

Ben

 

[Aladin Akyurek]

I have two worksheets in my workbook. First one is full of data (headings in row 1, each row from 2 downwards is a new record); second one pulls out certain records based on specified criteria.

In Sheet2:
Because the column headings change (relative references are not an option in this case), I'm using MATCH to identify the column in which to search where $D$1 is the heading I want it to look for:
MATCH($D$1,Data!$1:$1,0) -----> Returns column of required heading.

And then adding to that the row
ROW() -----> Returns row that the formula is in - the row numbers on each worksheet are consistent i.e. I want the formulas in Sheet2! row 4 to search in Data! row 4.

So piecing them together I'm using
INDIRECT("Data!"&ADDRESS(ROW(),MATCH($D$1,QryAll!$1:$1,0)))
to return the contents of the same row in the searched column in the first tab.

This part works.

Then I wanted to add a conditional element and tried this:
{=IF(INDIRECT("Data!"&ADDRESS(ROW(),MATCH($D$1,Data!$1:$1,0)))=X15:X17,TRUE,FALSE)}
E.g. say the column I search for using the MATCH is 'vehicle type liked', and the INDIRECT above returns 'motorcycle'. I'm trying to then search in X15:X17 for if the person likes one of the vehicles in the "acceptable list" - either a motorcycle, van or truck (those three entered in X15:X17).
When I enter the formula above and enter as an array, I get #Value!.

If I modify the formula to refer directly to the cell instead of using INDIRECT, it works
e.g {=IF(Data!C3=X15:X17,TRUE,FALSE)}; with "motorcycle" in Data!C3, it returns "TRUE".

When I put in the INDIRECT (which when evaluated on its own returns "Data!C3"), it doesn't work.

It seems that the formula falls over in the use of the INDIRECT but not otherwise in the sequence/construction of the formula - how can I make the IF in the array accept the INDIRECT?

Regards and thanks in advance,

Ben

Is it not possible to avoid INDIRECT at all? Could you post a small sample illustrative of the problem you want to solve?

 

[grenvold]

Aladin,

Thanks for your speedy reply!!!

INDIRECT could be avoided by putting in specific column references but the sheet will be used by people that don't know excel and may change a column heading/move things around which will make the columns incorrect so I'm trying to keep it as fluid as possible.

Example:

'Data' sheet
A B
1 Name Vehicle liked
2 Person1 motorcyle
3 Person2 van
4 Person3 car
5 Person4 van


Sheet2
A B X
1 Name Vehicle liked accepted vehicles
2 Person1 * motorcycle
3 Person2 * van
4 Person3 * truck
5 Person4 *

Then in B2 to B5 (* above):
{=IF(INDIRECT("Data!"&ADDRESS(ROW(),MATCH($B$1,Data!$1:$1,0)))=X2:X4,TRUE,FALSE)}
So it searches for the 'Vehicle liked' column on the first sheet, looks up what type they like (because it's the same row as the formula) and then goes "If it's in the accepted list, show TRUE; if not, show FALSE".

I realise there may be better ways to organise it but there are loads of columns and varying amounts of rows and added to that the fact that the end users probably can't modify for changes, it will need to follow this structure.

Thanks again.

 

[grenvold]

My formatting has been scrambled...

I both sheets, column A is name and B is vehicle liked (with vehicle types under it in the data sheet and * under it in sheet2).

In sheet two, column X is 'accepted vehicles' with motorcycle, van and truck under it.

 

[grenvold]

I have found a better solution than the approach above for anyone that reads this:

I was searching in my list of approved criteria. I have a finite list of options, half approved, half not. So, I was advised to use:

ISNA(MATCH( (search in list of options that are not approved) ))

and if it returns true, the record is not found in the list of rejected options and therefore should be counted...

Much better.

 

[Aladin Akyurek]

I have found a better solution than the approach above for anyone that reads this:

I was searching in my list of approved criteria. I have a finite list of options, half approved, half not. So, I was advised to use:

ISNA(MATCH( (search in list of options that are not approved) ))

and if it returns true, the record is not found in the list of rejected options and therefore should be counted...

Much better.

If you are after a count...

=SUMPRODUCT(1-ISNUMBER(MATCH(Range,ExcludeList,0)))

Note. The earlier posts were hard to "read" for concluding such. A small sample along with a desired outcome makes easier to describe a problem than discussing a possible formula.