• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Matching Text Using Array Formulas

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
 

Attachments

  • Chandoo Upload Example.xlsx
    15 KB · Views: 7
Back
Top