VLOOKUP? MATCH? HELP!

invisigirl · May 16th, 2002, 04:50 PM

Very challenged in the brightness department.

Okay, here's what I'm trying to do. I want to make a VERY SIMPLE worksheet where users who don't work in Excel can look up information. Here is the information involved:

UNITCODE DIAGNOSIS DESCRIPTION

I want the user to be able to type in a unit code and have it pull in all the diagnosis codes and test names (description) associated with it. Problem is, there can be 50 diagnosis codes for one unit code.

I know how to do a VLOOKUP, but not how to get it to display ALL the diagnosis codes and descriptions.

I'm sure this isn't that hard, but I'm frustrated because I had ideas on how to do it in Access, but the users don't have it installed.

Can any of you smarties help? Thanks!

Qroozn · May 16th, 2002, 04:53 PM

If you can use a Vlookup then why dont you copy that vlookup... and change the reference column it is returning?

Aladin Akyurek · May 16th, 2002, 04:56 PM

Quote:

On 2002-05-16 15:50, invisigirl wrote:
Very challenged in the brightness department.

Okay, here's what I'm trying to do. I want to make a VERY SIMPLE worksheet where users who don't work in Excel can look up information. Here is the information involved:

UNITCODE DIAGNOSIS DESCRIPTION

I want the user to be able to type in a unit code and have it pull in all the diagnosis codes and test names (description) associated with it. Problem is, there can be 50 diagnosis codes for one unit code.

I know how to do a VLOOKUP, but not how to get it to display ALL the diagnosis codes and descriptions.

I'm sure this isn't that hard, but I'm frustrated because I had ideas on how to do it in Access, but the users don't have it installed.

Can any of you smarties help? Thanks!

See my contrib in the thread:

http://www.mrexcel.com/board/viewtop...c=8352&forum=2

INDEX/MATCH combination can be faster (needed in your case) than VLOOKUP. It should not be too hard to adapt the concept to your situation.

invisigirl · May 16th, 2002, 05:10 PM

Well, it's gonna take me a while to read through all that and figure out how to adapt it to my spreadsheet, but thank you, Aladin!

I will dig this thread up tomorrow if it doesn't work.

Thanks!

invisigirl · May 16th, 2002, 06:05 PM

Darn it all! That's not gonna work for me because there are just too many different Unit Codes that exist out there. Like, hundreds. And along with each one of those, there can be 10, 20, 50, or 150 Diagnosis Codes that I need to display. I'm beginning to think this may be hopeless. If I have to send this to IS, sadly, it'll never get done.

Okay. I think I may have oversimplified the problem. I'd want my formula(s) to be looking at, say, column A. In column A, I would have to have as many duplicates of the same number as there are different Diagnosis Codes and Descriptions to the right in columns B and C.

Examples...Column A might look like this:

12345
12345
12345
12345
12345

Whereas Column B would look like this:

9.45
9.46
9.47
9.48
9.49

Column C is no problem because one unique diagnosis code matches up with one unique description.

Keep in mind that this spreadsheet is going to be probably at least a couple thousand rows long.

And remember, also, that the end user may have never used Excel before, so I need him/her to be able to just type in the Unit Code in one little cell, hit enter, and get all the results.

Now. From that tangled explanation...any takers? Am I fooling myself?

And now I have to catch the bus. Thank you for at least reading through my problem!

AJ

Aladin Akyurek · May 16th, 2002, 10:53 PM

Quote:

On 2002-05-16 17:05, invisigirl wrote:
Darn it all! That's not gonna work for me because there are just too many different Unit Codes that exist out there. Like, hundreds. And along with each one of those, there can be 10, 20, 50, or 150 Diagnosis Codes that I need to display. I'm beginning to think this may be hopeless. If I have to send this to IS, sadly, it'll never get done.

Okay. I think I may have oversimplified the problem. I'd want my formula(s) to be looking at, say, column A. In column A, I would have to have as many duplicates of the same number as there are different Diagnosis Codes and Descriptions to the right in columns B and C.

Examples...Column A might look like this:

12345
12345
12345
12345
12345

Whereas Column B would look like this:

9.45
9.46
9.47
9.48
9.49

Column C is no problem because one unique diagnosis code matches up with one unique description.

Keep in mind that this spreadsheet is going to be probably at least a couple thousand rows long.

And remember, also, that the end user may have never used Excel before, so I need him/her to be able to just type in the Unit Code in one little cell, hit enter, and get all the results.

Now. From that tangled explanation...any takers? Am I fooling myself?

And now I have to catch the bus. Thank you for at least reading through my problem!

AJ

Here is a sample of your data in sheet Database:

Microsoft Excel - aaMultValRetrieval invisigirl.xls_______________Running: xl2000 : OS = Windows (32-bit) NT 5.00
(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp
A8		=
*	A	B	C	D
1	UNITCODE	DIAGNOSIS	DESCRIPTION	*
2	12345	9.45	desc9.45	*
3	12345	9.46	desc9.46	*
4	12345	9.47	desc9.47	*
5	12345	9.48	desc9.48	*
6	12345	9.49	desc9.49	*
7	12346	10.1	desc10.1	*
8	*	*	*	*
Database

To see the formula in the cells just click on the cells hyperlink

The above image was automatically generated by [HtmlMaker V1.22]
If you want this code, click here and Colo will email the file to you.
This code was graciously allowed to be modified: by Ivan F Moala All credit to Colo

Here is what happens in Main when the user selects a UNITCODE in A2:

Microsoft Excel - aaMultValRetrieval invisigirl.xls_______________Running: xl2000 : OS = Windows (32-bit) NT 5.00
(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp
A6		=
*	A	B	C	D
1	UNITCODE	:alert('=MATCH(9.99999999999999E+307,Database!A:A)')>7	DIAGNOSIS	DESCRIPTION
2	12345	:alert('=ROW($A2:$D2)')>2	:alert('=IF(COUNTIF(Database!$A:$A,A$2),INDEX(Database!$B:$B,MATCH(A$2,Database!$A:$A,0)),"")')>9.45	:alert('=IF(LEN(C2),INDEX(Database!$C:$C,MATCH(C2,Database!$B:$B,0)),"")')>desc9.45
3	*	*	:alert('=IF(COUNTIF(Database!$A:$A,A$2)%3EROW()-$B$2,INDEX(Database!$B:$B,MATCH(A$2,OFFSET(Database!$A$1,MATCH(C2,Database!$B:$B,0),0,$B$1,1),0)+MATCH(C2,Database!$B:$B ,0)),"")')>9.46	:alert('=IF(LEN(C3),INDEX(Database!$C:$C,MATCH(C3,Database!$B:$B,0)),"")')>desc9.46
4	*	*	:alert('=IF(COUNTIF(Database!$A:$A,A$2)%3EROW()-$B$2,INDEX(Database!$B:$B,MATCH(A$2,OFFSET(Database!$A$1,MATCH(C3,Database!$B:$B,0),0,$B$1,1),0)+MATCH(C3,Database!$B:$B ,0)),"")')>9.47	:alert('=IF(LEN(C4),INDEX(Database!$C:$C,MATCH(C4,Database!$B:$B,0)),"")')>desc9.47
5	*	*	:alert('=IF(COUNTIF(Database!$A:$A,A$2)%3EROW()-$B$2,INDEX(Database!$B:$B,MATCH(A$2,OFFSET(Database!$A$1,MATCH(C4,Database!$B:$B,0),0,$B$1,1),0)+MATCH(C4,Database!$B:$B ,0)),"")')>9.48	:alert('=IF(LEN(C5),INDEX(Database!$C:$C,MATCH(C5,Database!$B:$B,0)),"")')>desc9.48
6	*	*	:alert('=IF(COUNTIF(Database!$A:$A,A$2)%3EROW()-$B$2,INDEX(Database!$B:$B,MATCH(A$2,OFFSET(Database!$A$1,MATCH(C5,Database!$B:$B,0),0,$B$1,1),0)+MATCH(C5,Database!$B:$B ,0)),"")')>9.49	:alert('=IF(LEN(C6),INDEX(Database!$C:$C,MATCH(C6,Database!$B:$B,0)),"")')>desc9.49
Main

To see the formula in the cells just click on the cells hyperlink

The above image was automatically generated by [HtmlMaker V1.22]
If you want this code, click here and Colo will email the file to you.
This code was graciously allowed to be modified: by Ivan F Moala All credit to Colo

The above assumes the unit codes are true numbers (that is, their underlying format is number, not text). Otherwise we need something different in B1.

The formula in C3 should be copied down to N rows where N is equal to the expected maximum number of diagnoses.
You can simply hide column B. The formula in D2 must be copied up to the row to which the previous formula has been copied.

Note. Instead of letting the user type a unit code in A2, let him/her choose from a dropdown list (set up with Data|Validation). The source of this dropdown must consists of a list of unique unit codes (look for examples of this at this board).

Aladin

PHMayfield · May 16th, 2002, 11:46 PM

Perhaps you can accomplish this with a pivot table, pivoting on the unit code. This would also give you a drop-down box (though you can type in directly as well).

RichardS · May 17th, 2002, 12:25 AM

Couldn't you just use autofilter? The user would only need to click on the drop down for Unit Code, select the code, and all the records for that code would be displayed.
Richard

invisigirl · May 17th, 2002, 10:13 AM

Well, all these are good suggestions, but what I really wanted to do was have the lookup feature in another workbook. I don't want the end user to have to (or, actually, be ABLE to) look at all the data. It will confuse them. Believe me.

Autofilter is the first thing I thought of, and it's what I'll do until I figure out something else, but it's really not what I want eventually.

But thanks for trying, everyone! Sorry to be such a pain!

invisigirl · May 17th, 2002, 10:20 AM

And, actually, now that I've gone and sounded like an idiot, I think (if I understand it correctly) I can probably adapt Aladin's strategy to be used on a whole new workbook, like I wanted. Just that when I tried it yesterday, it didn't work. But I will try again! I will never give up! Release the hounds!

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

May 16th, 2002, 04:50 PM	#1
invisigirl Board Regular Join Date: Mar 2002 Location: Oregon Posts: 130	Very challenged in the brightness department. Okay, here's what I'm trying to do. I want to make a VERY SIMPLE worksheet where users who don't work in Excel can look up information. Here is the information involved: UNITCODE DIAGNOSIS DESCRIPTION I want the user to be able to type in a unit code and have it pull in all the diagnosis codes and test names (description) associated with it. Problem is, there can be 50 diagnosis codes for one unit code. I know how to do a VLOOKUP, but not how to get it to display ALL the diagnosis codes and descriptions. I'm sure this isn't that hard, but I'm frustrated because I had ideas on how to do it in Access, but the users don't have it installed. Can any of you smarties help? Thanks!

May 16th, 2002, 04:53 PM	#2
Qroozn Board Regular Join Date: Mar 2002 Location: Sydney/Brisbane , Australia Posts: 539	If you can use a Vlookup then why dont you copy that vlookup... and change the reference column it is returning?

May 16th, 2002, 05:10 PM	#4
invisigirl Board Regular Join Date: Mar 2002 Location: Oregon Posts: 130	Well, it's gonna take me a while to read through all that and figure out how to adapt it to my spreadsheet, but thank you, Aladin! I will dig this thread up tomorrow if it doesn't work. Thanks!

May 16th, 2002, 06:05 PM	#5
invisigirl Board Regular Join Date: Mar 2002 Location: Oregon Posts: 130	Darn it all! That's not gonna work for me because there are just too many different Unit Codes that exist out there. Like, hundreds. And along with each one of those, there can be 10, 20, 50, or 150 Diagnosis Codes that I need to display. I'm beginning to think this may be hopeless. If I have to send this to IS, sadly, it'll never get done. Okay. I think I may have oversimplified the problem. I'd want my formula(s) to be looking at, say, column A. In column A, I would have to have as many duplicates of the same number as there are different Diagnosis Codes and Descriptions to the right in columns B and C. Examples...Column A might look like this: 12345 12345 12345 12345 12345 Whereas Column B would look like this: 9.45 9.46 9.47 9.48 9.49 Column C is no problem because one unique diagnosis code matches up with one unique description. Keep in mind that this spreadsheet is going to be probably at least a couple thousand rows long. And remember, also, that the end user may have never used Excel before, so I need him/her to be able to just type in the Unit Code in one little cell, hit enter, and get all the results. Now. From that tangled explanation...any takers? Am I fooling myself? And now I have to catch the bus. Thank you for at least reading through my problem! AJ

May 16th, 2002, 11:46 PM	#7
PHMayfield New Member Join Date: Apr 2002 Location: Phoenix, AZ, USA Posts: 29	Perhaps you can accomplish this with a pivot table, pivoting on the unit code. This would also give you a drop-down box (though you can type in directly as well).

May 17th, 2002, 12:25 AM	#8
RichardS Board Regular Join Date: Feb 2002 Location: Victoria, Australia Posts: 761	Couldn't you just use autofilter? The user would only need to click on the drop down for Unit Code, select the code, and all the records for that code would be displayed. Richard

May 17th, 2002, 10:13 AM	#9
invisigirl Board Regular Join Date: Mar 2002 Location: Oregon Posts: 130	Well, all these are good suggestions, but what I really wanted to do was have the lookup feature in another workbook. I don't want the end user to have to (or, actually, be ABLE to) look at all the data. It will confuse them. Believe me. Autofilter is the first thing I thought of, and it's what I'll do until I figure out something else, but it's really not what I want eventually. But thanks for trying, everyone! Sorry to be such a pain!

May 17th, 2002, 10:20 AM	#10
invisigirl Board Regular Join Date: Mar 2002 Location: Oregon Posts: 130	And, actually, now that I've gone and sounded like an idiot, I think (if I understand it correctly) I can probably adapt Aladin's strategy to be used on a whole new workbook, like I wanted. Just that when I tried it yesterday, it didn't work. But I will try again! I will never give up! Release the hounds!