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

[msampson]

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

 

[Sam_D_Ben]

Please try K2 "=IFERROR(INDEX($E2:$G2,MATCH(TRUE,COUNTIF($E2:$G2,$A$2:$A$6)>0,0)),"")"

 

[Peter_SSs]

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
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	ICD Code	Cat		pt	dxl	dx2	dx3	dx4	dx5		ICD Codes	Categories	Categories (no repeats)	ICD Codes (& Categories)
2	10	1		1	35	11	54				11	1	1	11 (1)
3	11	1		2	61	32
4	12	1		3	21	10	93				10, 21	1, 2	1, 2	10 (1), 21 (2)
5	21	2		4	52
6	22	2		5	72	43	92
7				6	51	37	12				12	1	1	12 (1)
8				7	12	21	10	12	36		10, 12, 21	1, 1, 2	1, 2	10 (1), 12 (1), 21 (2)
msampson

Cell Formulas
Range	Formula
K2:K8	K2	=TEXTJOIN(", ",1,FILTER(A$2:A$6,ISNUMBER(MATCH(A$2:A$6,E2:I2,0)),""))
L2:L8	L2	=TEXTJOIN(", ",1,FILTER(B$2:B$6,ISNUMBER(MATCH(A$2:A$6,E2:I2,0)),""))
M2:M8	M2	=TEXTJOIN(", ",1,UNIQUE(FILTER(B$2:B$6,ISNUMBER(MATCH(A$2:A$6,E2:I2,0)),"")))
N2:N8	N2	=TEXTJOIN(", ",1,FILTER(A$2:A$6&" ("&B$2:B$6&")",ISNUMBER(MATCH(A$2:A$6,E2:I2,0)),""))

 

[msampson]

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

 

[Peter_SSs]

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?

 

[Sam_D_Ben]

@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.

 

[Peter_SSs]

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.

 

[msampson]

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.

 

[Peter_SSs]

You're welcome. Glad it worked for you. Thanks for the follow-up.