Comapring fields that have TXT and No's

[Alisdair]

Please can someone help me here.

I have 3 columns.

1st column has Diagnosis Codes = 123.8 etc, but in no particular order.

2nd column has the complete diagnosis codes list, starting at one going on to 12490 fields.

3rd column relates to the text of the diagnosis code ie, if column 2 = 123.9 then column 3 would say Pregnancy Tests.

What I need is to compare column 1 with column 2 so I can get the correct text from column 3. This will then tell me what the diagnosis text is for column 1.

I know this is long winded but I need to know so I can keep my job. I have tried everything in my small head to get around this.

[EdMac]

Hi Alisdair,

It sounds as if you need to use VLOOKUP so that when you enter your code it will search for that code and return the text associated with it. Excel help is quite good on this function.

I think you need another column for the this i.e one column to enter your code, one to return the text and then two columns for the lookup data.

Ed

[Bondi]

Hi Alisdair,

Asuming that the first column is A , secound column is B and thrid is C
data starting in row 1 you could use Vlookup in column D to retrive the
diagnosis text for column A

=VLOOKUP(A1,$B$1:$C$12490,2)

You can copy this down in column D starting at D1. The column D should
match column A

Regards,
Bondi

[Alisdair]

Thank you both, but I am still struggling. I did look up the help, but that has confused me as all i get is N/A in the cells.
This does not come up right for you to look at But there is a gap between the numbers i.e 625.3 -- 1.1 -- Cholera

TRUE --- diag_code ------- diag_ldesc
465.9 1 Cholera due to Vibrio cholerae
625.3 1.1 Cholera due to Vibrio cholerae el tor
625.3 1.9 Unspecified cholera
789.09 2 Typhoid fever
789.09 2.1 Paratyphoid fever A
478.9 2.2 Paratyphoid fever B
478.9 2.3 Paratyphoid fever C
465.9 2.9 Unspecified paratyphoid fever
465.9 3 Salmonella gastroenteritis
847 3.1 Salmonella septicemia
847 3.2 Unspecified localized salmonella infection
847 3.21 Salmonella meningitis
723.1 3.22 Salmonella pneumonia
723.1 3.23 Salmonella arthritis
465.9 3.24 Salmonella osteomyelitis
465.9 3.29 Other localized salmonella infections
721.1 3.8 Other specified salmonella infections
721.1 3.9 Unspecified salmonella infection
721 4 Shigella dysenteriae
721 4.1 Shigella flexneri
721 4.2 Shigella boydii
721 4.3 Shigella sonnei
558.9 4.8 Other specified shigella infections
558.9 4.9 Unspecified shigellosis
558.9 5 Staphylococcal food poisoning
724.8 5.1 Botulism
724.2 5.2 Food poisoning due to Clostridium perfringens (C. welchii)
466 5.3 Food poisoning due to other Clostridia
466 5.4 Food poisoning due to Vibrio parahaemolyticus
463 5.81 Food poisoning due to Vibrio vulnificus
490 5.89 Other bacterial food poisoning
616.1 5.9 Unspecified food poisoning
616.1 6 Acute amebic dysentery without mention of abscess


So here it is or part of it. The first column is the column I need to match with the text on the last column. Therefor I need to get the correct match with column 2 i.e 1 looks for the same number in 2 and then copies the text it relates to in column 3 into a new colum that sit along side column 1!!!!!!

Jee whizz I am confusing myself. But that is it. Please can you have a look for me and tell me what I need to do.

Thanks

[EdMac]

OK lets see if you have got this straight

The lookup table can be anywhere in your spread sheet. From a user viewpoint I imagine you want to enter a number in a cell and up pops your text description so it may be better to locate your table out of the way.

In the lookup formula the first cell reference is the one you are entering the number in, the second is the range of the table i.e. First:Lastvalue. The third is the column number in the table - in this case 2. I would also suggest adding a fourth which is FALSE. All of these are separated by commas.

When entering the data that you want to look up the description for, spaces are OK between the digits, BUT what you are entering must exactly match a number in the lookup table - the N/A error normally means it can't find the result. If getting spaces in the wrong place causes problems it might be worth editing the data and the table to get rid of them - of course if there is need to keep them that is another matter.

If you continue to have problems, post back with a description of the problem as well as examples of what you have set up - you can always attach a ZIP file of your sheet.

Hope this helps

Ed