Share via

XLOOKUP formula

Anonymous
2024-12-01T14:52:12+00:00

Blood Pressure guidelines

Table7

Table7
Category SYS_Lower SYS_Upper AND _OR DIA_Lower DIA_Upper
Optimal 0 119 AND 0 79
Normal 120 129 AND/OR 80 84
High Normal 130 139 AND/OR 85 89
Isolated SYS HT 140 999 AND 0 89
Hypertension Grade 1 140 159 AND/OR 90 99
Hypertension Grade 2 160 179 AND/OR 100 109
Hypertension Grade 3 180 999 AND/OR 110 999

I want to retrieve category from the above table by using the following formula however it is returning incorrect value i.e. "Hypertension Grade 1" instead of "Isolated SYS HT". Please refer the following table and guide how to fix this issue and apply the and/or parameters

=XLOOKUP(1,([@SYS]>=SYS_Lower)*([@SYS]<=SYS_Upper)*(([@DIA]>=DIA_Lower)*([@DIA]<=DIA_Upper)+( [@DIA]<DIA_Upper)),Category,"No Match",1)

SYS DIA XLOOKUP Formula Required Result
125 77 Normal Normal
137 84 High Normal High Normal
158 92 Hypertension Grade 1 Hypertension Grade 1
145 88 Hypertension Grade 1 Isolated SYS HT
Microsoft 365 and Office | Excel | Other | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

8 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2024-12-02T06:38:41+00:00

    That still does not answer my question. 145 and 88 falls in multiple categories.

    Hi,

    SYS 145 fall in 2 categories however DIA 88 falls only in one category i.e. High Normal . Both the conditions met only in one category i.e. Isolated SYS HT. For "Isolated SYS HT" category booth the conditions are must as there is no "OR" condition. Further, in only 2 categorizes i.e. Optimal and Isolated SYS HT there is no OR condition rest have both the condition of and/or.

    Please also refer related orignal guideline in text form :-

    Optimal: Systolic blood pressure (SBP) less than 120 mm Hg and diastolic blood pressure (DBP) less than 80 mm Hg
    Normal: SBP of 120–129 mm Hg and/or DBP of 80–84 mm Hg
    Isolated SYS HT: SBP of 140 mm Hg or greater and DBP lower than 90 mm Hg
    High normal: SBP of 130–139 mm Hg and/or DBP of 85–89 mm Hg
    Grade 1: SBP of 140–159 mm Hg and/or DBP of 90–99 mm Hg
    Grade 2: SBP of 160–179 mm Hg or greater and/or DBP of 100–109 mm Hg
    Grade 3: SBP of 180 mm Hg or greater and/or DBP of 110 mm Hg or greater

    Table form :-

    Category SYS_Lower SYS_Upper AND _OR DIA_Lower DIA_Upper
    Optimal 0 119 AND 0 79
    Normal 120 129 AND/OR 80 84
    High Normal 130 139 AND/OR 85 89
    Isolated SYS HT 140 999 AND 0 89
    Hypertension Grade 1 140 159 AND/OR 90 99
    Hypertension Grade 2 160 179 AND/OR 100 109
    Hypertension Grade 3 180 999 AND/OR 110 999

    Was this answer helpful?

    0 comments
  2. Ashish Mathur 101.8K Reputation points Volunteer Moderator
    2024-12-02T03:13:24+00:00

    That still does not answer my question. 145 and 88 falls in multiple categories.

    Was this answer helpful?

    0 comments
  3. Anonymous
    2024-12-02T02:46:52+00:00

    Hi,

    For the first row shown in your result table, why should the answer be Normal? Why not Hypertension Grade 3, Hypertension Grade 2, Hypertension Grade 1, High Normal?

    Hi,

    I have tested all the five categories, revised table appended for your review. The error occur in just one category highlighted in red fonts.

    SYS DIA XLOOKUP Formula Required Result
    185 115 Hypertension Grade 3 Hypertension Grade 3
    165 105 Hypertension Grade 2 Hypertension Grade 2
    145 95 Hypertension Grade 1 Hypertension Grade 1
    145 88 Hypertension Grade 1 Isolated SYS HT
    135 88 High Normal High Normal
    125 83 Normal Normal
    118 78 Optimal Optimal

    Was this answer helpful?

    0 comments
  4. Ashish Mathur 101.8K Reputation points Volunteer Moderator
    2024-12-01T23:42:25+00:00

    Hi,

    For the first row shown in your result table, why should the answer be Normal? Why not Hypertension Grade 3, Hypertension Grade 2, Hypertension Grade 1, High Normal?

    Was this answer helpful?

    0 comments
  5. Anonymous
    2024-12-01T20:16:52+00:00

    I struggled with your lookup table to get the same results as you expect. The problem for me was the AND/OR rows.

    I haven't thought this through thoroughly but I may have a solution. First I added some rows for the AND/OR rows (2 more for each such row) where I'd allow the full range of pressures on the diastolic side and on a separate row the full range of pressures on the systolic side.

    Category SYS_Lower SYS_Upper AND _OR DIA_Lower DIA_Upper
    Isolated SYS HT 140 999 AND 0 89
    Hypertension Grade 3 180 999 AND/OR 110 999
    Hypertension Grade 3 0 999 AND/OR 110 999
    Hypertension Grade 3 180 999 AND/OR 0 999
    Hypertension Grade 2 160 179 AND/OR 100 109
    Hypertension Grade 2 0 999 AND/OR 100 109
    Hypertension Grade 2 160 179 AND/OR 0 999
    Hypertension Grade 1 140 159 AND/OR 90 99
    Hypertension Grade 1 0 999 AND/OR 90 99
    Hypertension Grade 1 140 159 AND/OR 0 999
    High Normal 130 139 AND/OR 85 89
    High Normal 130 139 AND/OR 0 999
    High Normal 0 999 AND/OR 85 89
    Normal 120 129 AND/OR 80 84
    Normal 120 129 AND/OR 0 999
    Normal 0 999 AND/OR 80 84
    Optimal 0 119 AND 0 79

    Now I look for rows that fulfil the requirements using AND for all rows in the table, and often there are multiple rows, so I take one of them. Which one? The first one! This means that the rows in the lookup table are in order of severity (the one you prefer to see among the multiple rows returned).

    Table7's AND_OR column is not necessary but I left in it for your reference only.

    So the formula I ended up with is:

    =TAKE(FILTER(Table7[Category],([@SYS]>=Table7[SYS_Lower])*([@SYS]<=Table7[SYS_Upper])*([@DIA]>=Table7[DIA_Lower])*([@DIA]<=Table7[DIA_Upper]),"none"),1)

    Image

    I have NOT tested this thoroughly, I'll leave that to you.

    See the linked-to workbook below:

    https://app.box.com/s/mydilsv115cz1fmdmm8yi1qew8bctbfy

    Was this answer helpful?

    0 comments