using functions to compare multiple columns for mismatch of cells

Hmmm... having only 10 words as a subject to describe this problem seems
rather limiting. Come on, Microsoft, let me have 15 words!

I want to use Excel functions to compare multiple columns for mismatch of
cell contents. I spoent a lot of time using Excel Help for functions like
VLOOKUP and IF, with no luck. I assume there is a functional solution, but I
could not figure it out in an hour. These are not easy functions for me to
use.

The TASK: compare name (text) AND a number in a set of columns with similar
(but not exact) info in another set of columns. I want to be able to have a
text string (ex: "Not in Bonus column") AS WELL AS a notation like "Number
mismatch".
There are more entries in one set of columns than another.

After much research, and trial & error, I was unsuccessful using a single
function to get the desired results.

I tried consolidating the text & number information in an additional column
using this formula: =B403&" "&F403 - info was in columns B & F

Then, I used: =VLOOKUP(BI402,$G$10:$H$433,1,FALSE) to compare the
consolidated info. The result was showing as, "#N/A", but it did not
distinguish between a mismatch of cell contents, or if matching information
was missing from a column.

Can you help me with this?

Mark D.
Boston, MA

#N/A in this situation would mean that no match could be found in
column G of the lookup table. You could trap this using a construction
like =IF(ISNA(lookup_formula),"no match",lookup_formula).

Your vlookup formula has a third parameter of 1, meaning to return data
from the first column of the table - presumably you intended this to be
2? If it was set to 2 and there was a match on column 1 of the table
but no data in the adjacent column, you would get a returned value of
0, even if the cell was empty.

Hope this helps.

Pete

For vlookup, Lookup_value (the first cell you put in the formula) is the
value to be found in the first column of the array-meaning you have to use
column A for the value to lookup, otherwise vlookup won't work. You can only
compare one item at a time, but you can just copy the vlookup formula over to
the next column.

"MDIAZ451" wrote:

> Hmmm... having only 10 words as a subject to describe this problem seems
> rather limiting. Come on, Microsoft, let me have 15 words!
>
> I want to use Excel functions to compare multiple columns for mismatch of
> cell contents. I spoent a lot of time using Excel Help for functions like
> VLOOKUP and IF, with no luck. I assume there is a functional solution, but I
> could not figure it out in an hour. These are not easy functions for me to
> use.
>
> The TASK: compare name (text) AND a number in a set of columns with similar
> (but not exact) info in another set of columns. I want to be able to have a
> text string (ex: "Not in Bonus column") AS WELL AS a notation like "Number
> mismatch".
> There are more entries in one set of columns than another.
>
> After much research, and trial & error, I was unsuccessful using a single
> function to get the desired results.
>
> I tried consolidating the text & number information in an additional column
> using this formula: =B403&" "&F403 - info was in columns B & F
>
> Then, I used: =VLOOKUP(BI402,$G$10:$H$433,1,FALSE) to compare the
> consolidated info. The result was showing as, "#N/A", but it did not
> distinguish between a mismatch of cell contents, or if matching information
> was missing from a column.
>
> Can you help me with this?
>
> Mark D.
> Boston, MA
>
>

If you can change the columns into text file, then you can try Texteer
software at: http://www.texteer.com
It provides several functions in which "Include Compare" function maybe
can solve your problem.The quote
page:http://www.texteer.com/windows/b0.sb1.1.htm