Column Search

S

Spellbound

Hi

I would like to know if it is possible to do the following in Excel
2003.

I have a number of columns of data which are used by various
worksheets.

My latest dilema is how do I find the first occurance of two different
numbers in two column of figures and match it with a value in an
adjacent cell; which will be the result of a formula.

In other words I want to find the 1st occurance of 1234 in column A
along with the first occurance of 789 in column B and where they match
return the value in column C.

Hoping for a speedy solution ...thanks
 
D

Dave Peterson

If a2 contains 1234 and b2 contains 789, you can use something like:

=index(othersheet!$c$1:$c$100,
match(1,(a2=othersheet!$a$1:$a$100)*(b2=othersheet!$b$1:$b$100),0))
(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't use the whole column.

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))

(still an array formula)
 
S

Spellbound

Hi Dave

Thanks for the tip. I had actually tried to make Index and Match work
but somehow did not get it quite right but your reply helped me to sort
it out.

You are right about not being able to use the whole column as A:A, so I
have followed your example and simply used A1:A9999 although this will
have to be increased at some point as I input about 400 lines of data
per week.

In the end I found that there were 2 basic formulas I could use to
resolve this based upon your solution.

One being to index the particular column that I need the result for as
in:

{=INDEX(E2:E9999,MATCH(othersheetG4&othersheetH24,B2:B9999&C2:C9999,0))}

or

{=INDEX(A2:G9999,MATCH(othersheetG4&othersheetH24,B2:B9999&C2:C9999,0),7)}

where you index a number of columns and use the column number at the
end to reflect source of the results; which means you can obtain
multiple results from virtually the same formula.

Both work, although not sure which is the cleaner/faster solution.

Cheers!
 
D

Dave Peterson

I don't like to use the formulas that concatenate the values.

othersheet!G4&othersheet!H24

These kinds of things in G4 and H24 could cause trouble:
XYYY
X YYY
XY YY
XYY Y
XYYY

All these will look the same when you join them together.

If you know your data, it may not be a problem (for a while anyway, <bg>).
 
R

Roger Govier

Hi

If I do have to use concatenate, then to get around the problem Dave
mentions, then I insert the Pipe character (above the backslash symbol
on the keyboard) into the concatenation.

othersheetG4&"|"&othersheetH4
It make the formulae a bit longer, but it does help when trying to
distinguish what come from each field.
 
S

Spellbound

Hi all

Thanks for the tips.

I was a bit surprised at the comments regarding the possibility of the
values being concatenated; which I assume is due to the '&' in my
formulas. I came across the use of this tip on another site which put
this method forward as the means for testing 2 variables in a MATCH
formula.

However, whilst I can make the Index/Match formulas work in various
ways; I am now concerned that the number and size of all these formulas
may be too excessive; especially as the the Index/Match formula would
have to incorporated within an IF formula to test the result and carry
out an alternative function if false.

Perhaps I should now consider making more use of the Pivot Table by
expanding the data controlled by the table. Again not an expert on
these things, even though I managed to make the current one work; not
quite sure now how I would alter it to create the results that I need!

Suddenly things have become more complicated instead of simpler ;)
 
Top