VLOOKUP or IF or BOTH

[[email protected]]

All,

I've been puzzling over this one for a while so I'd appreciate some
fresh eyes taking a look (and hopefully saying "that's so easy" and
proposing something so simple I slap myself in the forehead...*wry
grin*).

In A10:A17 I have a list of sample IDs for two sorts of water (muddy vs
clean). In B10:B17 I have a list of the samples that they were mixed
with.

1 X
2 5
3 4
4 3
5 2
6 17
7 8
8 7

As you can see, sample 5 mixed with sample 2, sample 1 didn't mix with
any samples, sample 6 mixed with sample 17 (this is just a snippet of
the values), etc. Let's say that we know sample 2 has a particular
feature (eg: it's muddy water). Because it mixed with sample 5 (clean
water), sample 5 is now the same as sample 2 (muddy water).

Since I know sample 5's status to begin with, a simple IF statement
within the table is fine to show sample 5 as "M" in column C. What I
would like to do is to somehow get the value from column B (ie: 2) and
then make sample 2 also "M" status. (Note: If two clear water samples
mix (eg: samples 7 and 8) they should remain clear and stay as "C"
status).

I've tried this in multiple columns and within the same column with no
success using VLOOKUP and IF combinations. I'm finding the problem
seems to be that Excel runs through the formula to determine the status
of sample 2 but then doesn't re-evaluate the status after sample 5 is
found to be of a different status. Can someone shed some light on
other functions I might try or if I'm just making some stupid mistake
and you can get those functions I mentioned to work without a problem?

Thanks in advance!

[Carim]

Hi,

>From what I understand, the function sumproduct() would do the job ...
Should you provide more details, with an example of what appears in A10
and B10, problem could be quickly solved.

HTH
Cheers
Carim

[[email protected]]

Carim,

Thanks for the prompt reply and apologies for my question requiring
further information. I was loathe to make the question text too long
because then people might not read it. Some further information...

The following range is B9:F17 including column headings

~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Sample ID Mixed with Original status Vlookup Final status
1 X C #N/A #N/A
2 5 C M M
3 4 C C C
4 3 C C C
5 2 M C M
6 17 C C C
7 8 C C C
8 7 C C C

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

As can be seen in column D ("Original status"), sample 5 was
originally muddy (M) when it mixed with sample 2 containing clear water
(C).

Column E ("Vlookup") contains a VLOOKUP function...
=VLOOKUP(C10,$B$10:$F$17,3)

Column F ("Final status") contains an IF statement...
=IF(D10="M","M",IF(E10="M","M","C"))

What I'd really like to be able to do is to somehow eliminate columns E
and F and have those calculations performed within column D. Is this
possible? It's not a critical matter in terms of the functionality of
the worksheet - I can use the multiple column method if I have to.
However, it seems to me that it would be far neater to get all the
calculations done within a single cell rather than three cells but I
cannot get the syntax correct for it to work.

Hope that makes more sense now.