I have two columns of accounting codes. One is a fixed list of unique codes; the other is a list of entries with many duplicates. I can summarise using the SUMIF function but what I want to do is identify any entries in the variable list that don't correspond to the fixed list (in case somebody introduces a spurious or new code). How can I do it?
2006-06-13 21:59:51 · 4 answers · asked by Anonymous in Computers & Internet ➔ Programming & Design
You need to do a lookup. If you type this into excel is should popup a wizard.
2006-06-13 22:08:34 · answer #1 · answered by MSX 1 · 1⤊ 2⤋
Let's say the fixed list is in column A and the other list is in column B.
Assuming the first code of the B column is in cell B2, type in the following formula in cell C2:
= ISNA(VLOOKUP(B2,A:A,1,false))
And then fill down that formula to get it in front of each code of the column B.
It will return TRUE each time a code not existing in the fixed list is found, and FALSE the rest of the time.
Explanation of the formula:
The VLOOKUP is looking for a value associated to a specified element (first argument) in a specified range (second argument) placed in the nth column of that range (third argument). 4th argument is about the matching options. It retuns a #N/A error if the element is not found.
ISNA just checks the presence of a #N/A error; if such an error is found in the evaluated formula, it returns TRUE.
2006-06-14 05:36:51 · answer #2 · answered by Castalian Tourist 5 · 0⤊ 0⤋
Yes, You can use VLookup function here.
But
Match function will fit better
Put this function in E1
=if(iserror(match(d1;C:C;0));"Not Found";"Found")
and fill it down...
Assuming that Column C has the fixed codes< column D has the vatriable codes.
I am the XLMan
2006-06-14 08:43:50 · answer #3 · answered by Anonymous · 0⤊ 0⤋
I'd use an IF, Else formula to return something that is recognizable as an error... I use something similar myself...
Only issue you will get is Excel only allows maybe 8 in a single cell... Multiple cell need to be used for longer statements...
Does that help?
=IF(C184="CUMBR",3,IF(C184="CONBM",3,IF(C184="INGBM",3,IF(C184="MURBM",3,IF(C184="KBMR",3,IF(C184="TULRB",2,IF(C184="TORBM",3,IF(C184="MELB",2,N184))))))))
Somthing like this is what I use.... Obviously each else could be the same error code for you....
2006-06-14 05:07:42 · answer #4 · answered by Mr_Moonlight 4 · 0⤊ 0⤋