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

grenvold

New Member
Joined
Oct 29, 2006
Messages
6
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
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
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?
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top