BigBuilder
New Member
Hi,
I am trying to use an array formula to return a value based on matching 2 text fields.
The first array contains the following values:
Columns/Row:
V8 - AM8 contain:
E-Obs Mob Working-Devices at the point of care Wifi SSO BMDI E-Rostering Remote Monitoring - Clinician to Patient Care plans "Order Comms
Pathology" "Order Comms
Radiology" "Order comms
Others" Patient Flow Asset Tracking E-Noting/Clin Docs Bed Mgmnt E-PX - Inpatients E-PX - Discharge E-Px - Outpatients
Columns/Row:
V10 - AM10 contain:
21% - 40% Neither Agree nor Disagree Neither Agree nor Disagree Neither Agree nor Disagree Disagree Completely Mostly Agree Somewhat Disagree 81% - 100% 0 0 0 Neither Agree nor Disagree Disagree Completely 81% - 100% Mostly Agree 1% - 20% 1% - 20% 0
I'm trying to pull out all the 'Neither Agree nor Disagree' texts from row 10 and then pulling out the corresponding text from the 'header' row: row 8.
I'm using this array formula:
=IF($V$8:$AM$8='Working Area'!G$6,IF($V$10:$AM$10='Working Area'!$B$7,+'Working Area'!G$6,""))
where 'Working Area'!G$6 contains the header row text and 'Working Area'!$B$7 contains, (for the purposes of this query), 'Neither Agree nor Disagree', (not, obviously in quotes).
Basically, the formula returns less than not a lot, i.e., a False, yet both conditions should match.
Checks I've made.
Excel recognises the two fields as text, despite both of them having the General format.
Excel acknowledges that both sets of match are equal i.e., if I enter =if($v$8='Working Area'!G$6,"Y","N"0, it returns a "Y", and similarly for 'Working Area'!B$7 and cell V10.
Both Vlookup and Hlookup work fine in matching using either 'Working Area'!B$7 and 'Working Area'!G$6.
Here's another puzzling thing. "Neither Agree nor Disagree" appears first in cell W10. If I alter the array formula to start in column W, it does return "Mob Working-Devices at the point of care" - which it should.
Now, I've seen this before but have never worked out why it happens: if the array formula gets a match first-off, it works. If it gets a False first-off, it doesn't and, it only appears to happen when trying text matches.
So, there's bound to be a lot of good theories/suggestions/solutions out there and I look forward to receiving them.
I've uploaded a sample file so the layout's a bit clearer.
Obviously the rows/columns won't exactly match to the formulas but, hopefully, it does give an idea as to what I'm trying to achieve.
And, I'm praying that I haven't made a schoolboy error !
Thanks
I am trying to use an array formula to return a value based on matching 2 text fields.
The first array contains the following values:
Columns/Row:
V8 - AM8 contain:
E-Obs Mob Working-Devices at the point of care Wifi SSO BMDI E-Rostering Remote Monitoring - Clinician to Patient Care plans "Order Comms
Pathology" "Order Comms
Radiology" "Order comms
Others" Patient Flow Asset Tracking E-Noting/Clin Docs Bed Mgmnt E-PX - Inpatients E-PX - Discharge E-Px - Outpatients
Columns/Row:
V10 - AM10 contain:
21% - 40% Neither Agree nor Disagree Neither Agree nor Disagree Neither Agree nor Disagree Disagree Completely Mostly Agree Somewhat Disagree 81% - 100% 0 0 0 Neither Agree nor Disagree Disagree Completely 81% - 100% Mostly Agree 1% - 20% 1% - 20% 0
I'm trying to pull out all the 'Neither Agree nor Disagree' texts from row 10 and then pulling out the corresponding text from the 'header' row: row 8.
I'm using this array formula:
=IF($V$8:$AM$8='Working Area'!G$6,IF($V$10:$AM$10='Working Area'!$B$7,+'Working Area'!G$6,""))
where 'Working Area'!G$6 contains the header row text and 'Working Area'!$B$7 contains, (for the purposes of this query), 'Neither Agree nor Disagree', (not, obviously in quotes).
Basically, the formula returns less than not a lot, i.e., a False, yet both conditions should match.
Checks I've made.
Excel recognises the two fields as text, despite both of them having the General format.
Excel acknowledges that both sets of match are equal i.e., if I enter =if($v$8='Working Area'!G$6,"Y","N"0, it returns a "Y", and similarly for 'Working Area'!B$7 and cell V10.
Both Vlookup and Hlookup work fine in matching using either 'Working Area'!B$7 and 'Working Area'!G$6.
Here's another puzzling thing. "Neither Agree nor Disagree" appears first in cell W10. If I alter the array formula to start in column W, it does return "Mob Working-Devices at the point of care" - which it should.
Now, I've seen this before but have never worked out why it happens: if the array formula gets a match first-off, it works. If it gets a False first-off, it doesn't and, it only appears to happen when trying text matches.
So, there's bound to be a lot of good theories/suggestions/solutions out there and I look forward to receiving them.
I've uploaded a sample file so the layout's a bit clearer.
Obviously the rows/columns won't exactly match to the formulas but, hopefully, it does give an idea as to what I'm trying to achieve.
And, I'm praying that I haven't made a schoolboy error !
Thanks