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 particula
order.

2nd column has the complete diagnosis codes list, starting at one goin
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 th
correct text from column 3. This will then tell me what the diagnosi
text is for column 1.

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

 

[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

 

[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

 

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

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