(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
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,138
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

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

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

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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,138
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.
 

Forum statistics

Threads
1,081,690
Messages
5,360,614
Members
400,592
Latest member
badgergurl

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top