can I do a lookup to search a range of cells for any one of a list of values?

msampson

Board Regular
Joined
Mar 9, 2004
Messages
129
Office Version
  1. 365
Platform
  1. MacOS
I have a named list of 35 ICD code values (the second column in this named list is the category of result just to simplify the 35 down to 15). I have a dataset with 500,000 rows, each a person, and multiple columns of data. If a person only had one diagnosis they will only have one column of data with maybe one of my 35, but also possibly something different (other type of ICD code). One person in this database has 258 different diagnosis codes, so everyone has potentially 258 columns to look through, but if they have 5, only the first 5 will have data and other 253 will be empty. Is there a way I can do a search of the 258 columns for any one of my 35 codes? Then I can return either the code or the category (2nd column of my named list). I'm just trying to find out how many of these 500,000 people had heart disease regardless of whether or not they had other any diagnosis. I can just return the first instance of my list and don't need all the times they subsequently may have had something else from my list.

list
10 . 1
11 . 1
12 . 1
21 . 2
22 . 2
etc

data
pt. . dx1 . dx2 . dx3
1 . 35 . 11 . 54
2. 61 . 32
3. 21 . 10 . 93
4. 52
5. 72 . 43 . 92
6. 51 . 37 . 12

a very rough idea. Pt 1 has an 11 so that's a hit, I can get either 11 or 1. Pt 2 has none, P3 has 2, but I only need the first one and can get either 21 or 2. Pt 4 and 5 don't have any and Pt 6 has one so I can get either 12 or 1.

Just not sure how to expand an HLOOKUP to not just check a single cell but anything from a list of values.
thanks, Maureen
Mac Excel version 16.71 from MS365
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
Please try K2 "=IFERROR(INDEX($E2:$G2,MATCH(TRUE,COUNTIF($E2:$G2,$A$2:$A$6)>0,0)),"")"

1684207269068.png
 
Upvote 0
Here are a few options depending on whether you want ICD codes, Categories, Categories without repeats or Codes & Categories.

With such large data, you may find these formulas make your worksheet sluggish. If that is the case and you would be happy to have a macro suggestion instead then post back with that request & also identify which of the result types you want.

23 05 16.xlsm
ABCDEFGHIJKLMN
1ICD CodeCatptdxldx2dx3dx4dx5ICD CodesCategoriesCategories (no repeats)ICD Codes (& Categories)
21011351154111111 (1)
311126132    
4121321109310, 211, 21, 210 (1), 21 (2)
5212452    
62225724392    
76513712121112 (1)
87122110123610, 12, 211, 1, 21, 210 (1), 12 (1), 21 (2)
msampson
Cell Formulas
RangeFormula
K2:K8K2=TEXTJOIN(", ",1,FILTER(A$2:A$6,ISNUMBER(MATCH(A$2:A$6,E2:I2,0)),""))
L2:L8L2=TEXTJOIN(", ",1,FILTER(B$2:B$6,ISNUMBER(MATCH(A$2:A$6,E2:I2,0)),""))
M2:M8M2=TEXTJOIN(", ",1,UNIQUE(FILTER(B$2:B$6,ISNUMBER(MATCH(A$2:A$6,E2:I2,0)),"")))
N2:N8N2=TEXTJOIN(", ",1,FILTER(A$2:A$6&" ("&B$2:B$6&")",ISNUMBER(MATCH(A$2:A$6,E2:I2,0)),""))
 
Last edited:
Upvote 0
Solution
Please try K2 "=IFERROR(INDEX($E2:$G2,MATCH(TRUE,COUNTIF($E2:$G2,$A$2:$A$6)>0,0)),"")"

View attachment 91668
OK, I tried this one, because I didn't think to mention that ICD 9 codes are just numbers, but when I switch over to ICD 10 codes they are alphanumeric (i.e. E110, Y270). The other solution specifically mentions isnumber so I tried this one first. It seems to work with the ICD9 codes, returning the number 0 or "". Which is fine, I can count the 0s. But it breaks for the ICD10 codes. Sometimes it returns a 0 and sometimes the code from the list (E310). But - sometimes it returns one of the patient codes which is not in the list. Not sure why the difference and why it sometimes is returning a nonlist item.
thanks, Maureen
 
Upvote 0
OK, I tried this one, ..
.. and it apparently didn't quite do what you wanted, so why not try the other suggestion?

The other solution specifically mentions isnumber
The ISNUMBER in my formulas has nothing to do with the nature of the codes.

If neither suggestion works, then please provide some sample data that is more realistic and the expected results, preferably with XL2BB so that we can see exactly what is in what cells and can also easily copy for testing with that actual sample data?
 
Upvote 0
@Peter_SSs Mine doesnt work and I tired yours, and it is working, besides L4 is picking up both 21&10. Its is suppose to be 21 as per requirement.
1684282384478.png
 
Upvote 0
I tired yours,
The formula in your image isn't the same as any of mine.

I know that the OP said they only needed the first match if there is more than one, however, I was providing a suggestion that returns them all in case others are of interest.
 
Upvote 0
sorry for the delay, was out sick. I tried this one
=TEXTJOIN(", ",1,FILTER(A$2:A$6,ISNUMBER(MATCH(A$2:A$6,E2:I2,0)),""))
and it worked beautifully. Thanks for the help.
 
Upvote 0
You're welcome. Glad it worked for you. Thanks for the follow-up. :)
 
Upvote 0

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

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