Searching for an attay within a data set

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I have 3 cells that contain a set of values, the total of which will always
be constant eg:
A B C
1 0 2 1

I have a table populated with all possible combinations of these numbers:

0 0 3 n1
0 1 2 n2
1 1 1 n3
0 2 1 n4
etc. etc.

There is a fourth column to the right of this table with a set of
unconnected (and not necessarily unique) numbers (n).

How can I search the full table for the single occurrence of the pattern in
cells A B and C and return the value in column 4 - n4 in this case?

Many thanks

Dave
 
Dave,

In D1 write something like this: Not Found

In an other cell place this formula:

=INDIRECT("D" & 1*(SUMPRODUCT(ROW(A3:A6)*((A3:A6 & B3:B6 & C3:C6)=(A1 & B1 &
C1)))=0)+SUMPRODUCT(ROW(A3:A6)*((A3:A6 & B3:B6 & C3:C6)=(A1 & B1 & C1))))

But perhaps there is a more simple solution.

Jan
 
Since your rows in your table are all unique combinations and since n1, ..., nx
are all numbers, you could use:

=sumproduct(--(a1=sheet2!a1:a64),
--(b1=sheet2!b1:b64),
--(c1=sheet2!c1:c64),
(sheet2!d1:d64))

If the rows were unique combinations, but the n1, ..., nx were strings, you
could use something like:

(saved from a previous post)

If you want exact matches for just two columns (and return a value from a
third), you could use:

=index(othersheet!$c$1:$c$100,
match(1,(a2=othersheet!$a$1:$a$100)*(b2=othersheet!$b$1:$b$100),0))

(all in one cell)

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can only use the whole column in xl2007.

This returns the value in othersheet column C when column A and B (of
othersheet) match A2 and B2 of the sheet with the formula.

And you can add more conditions by just adding more stuff to that product
portion of the formula:

=index(othersheet!$d$1:$d$100,
match(1,(a2=othersheet!$a$1:$a$100)
*(b2=othersheet!$b$1:$b$100)
*(c2=othersheet!$c$1:$c$100),0))
 
Back
Top