Lookup table vs Index vs IFSEARCH Help

Hi,
I have a spreadsheet containing numeric exam codes, and their descriptive names among other data. I want to categorize the exams as either Screening or diagnostic. I have tried to do this 3 ways with no success, help!
First I tried an index with the exam codes but there seem to be too many in the range...
Next I tried if the cell contains "screening" it returns screening, if not it is"diagnostic" Couldn't get that to work
Last I tried a lookup table and it doesn't seem to recognize the lookup value to find it in the referenced range. I tried making sure both fields are in number format... no luck.
Now I'm just really frustrated!

table is in the second tab

Originally Posted by AliGW

This will work:

=VLOOKUP([@CODE],Table2,2,0)

but not until you have got rid of the leading spaces in the column containing the codes next to it.

Which column has leading spaces?

The code in"Exams" appeeared to be TEXT while those in Sheet2 are numeric.

Correcting codes in "Exams" produced matches using VLOOKUP although many codes are missing.

Originally Posted by AliGW

The one referenced in the first part of the VLOOKUP formula. Column I on the exams sheet.

Thanks! that worked.

Select COLUMN I

Data==>Text-to-Columns==> third screen=>General=> Finish

Then

=IFERROR(VLOOKUP(I2,Table2[#All],2,FALSE),"")

should work.

the data is coming from and ODBC connection so

Originally Posted by hwminich

the data is coming from and ODBC connection so

Yes! thank you