View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
CraigSA
 
Posts: n/a
Default vlookup vs if, help required

hi;

I have this sheet that I am making where there are 5 test results, each with
3 possible inputs. I am using optionbuttons to enter the results. So I end up
with a table that looks something like this:

true false false
true false false
true false false
true false false
true false false
Where true values could swap with either of the false values in each row.

Now for my application I need to read these results to get an output, so
there are quite a few possible combinations, like 250 if I calculated right!
There are cases though that will give the same results so like, if assuming
we start at a1, I could have only one result if a1=true and b2=true,
regardless of the other three rows values, so this reduces the number of
outcomes quite a lot.

I have used if statements for most of the basic results but the whole 7
bracket limit issue is a bit of a problem for more specific cases. I am
familiar with vlookup and referencing a lookup table, but checking 15
true/false values at once seems like a long way around.
eg. vlookup(a1&b1&c1&a2 ... c5 , reference table , 2 , 0)
then in the reference column 1 will have all possible results
eg. truetruefalsetrue...(up to 15th true/false entry) and column 2 will have
the text to display following each outcome.
This is definitely possible but is this the best way to go about it or is
there an easier method??