Arrays Containing Data from Multiple Worksheets

  • Thread starter Thread starter Bubbis Thedog
  • Start date Start date
B

Bubbis Thedog

Maybe I'm just experiencing a brain fart, but I can't remember how to
input arrays from multiple worksheets into a formula. Here's what I
want to do...

WS1: E5:E40
WS2: E5:E41

I'm trying to combine the data from both worksheets for a MATCH formula:

=MATCH(I15,[WS1!E5:E40,WS2!E5:E41 combined],FALSE)

The second argument is obviously what I'm having difficulty with. Any
help is greatly appreciated.



** Posted via: http://www.ozgrid.com
Excel Templates, Training, Add-ins & Business Software Galore!
Free Excel Forum http://www.ozgrid.com/forum ***
 
Bubbis,

I think you'll need to combine two separate MATCH functions, in the form:

=IF(ISERROR( Match(1) ), Match(2), Match(1) )

This is untested, but try something of the form:

=IF(ISERROR(MATCH(I15, [WS1]!E5:E40,FALSE)), MATCH(I15, [WS2]!E5:E41,
FALSE), MATCH(I15,[WS1]!E5:E40, FALSE))
 
What do you expect a function to return if the value from I15 is in E7
of WS1? And what do you expect it to return if the value from I15 is
instead in E7 of WS2?

Alan Beban
 
Earl said:
Bubbis,

I think you'll need to combine two separate MATCH functions, in the form:

=IF(ISERROR( Match(1) ), Match(2), Match(1) )

This is untested, but try something of the form:

=IF(ISERROR(MATCH(I15, [WS1]!E5:E40,FALSE)), MATCH(I15, [WS2]!E5:E41,
FALSE), MATCH(I15,[WS1]!E5:E40, FALSE))
Click to expand...

??
Aside from the fact that the syntax [WS1],[WS2] doesn't work, assuming
the value from I15 was in a cell other than E41 on WS2, how would the
user ever tell whether the value from I15 was found on WS1 or on WS2???

Alan Beban
 
Alan,

You could add the count of entries in one table to the result of the other,
making it look as if they were one big table. You could then determine
which table, if needed, by the range into which the answer falls. Or maybe
you could add a large number to make it simple, and do something similar.

=IF(ISERROR( Match(1) ), Match(2) + Number, Match(1) )

I didn't really know where the OP was going with this.
--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

Alan Beban said:
Earl said:
Bubbis,

I think you'll need to combine two separate MATCH functions, in the form:

=IF(ISERROR( Match(1) ), Match(2), Match(1) )

This is untested, but try something of the form:

=IF(ISERROR(MATCH(I15, [WS1]!E5:E40,FALSE)), MATCH(I15, [WS2]!E5:E41,
FALSE), MATCH(I15,[WS1]!E5:E40, FALSE))
Click to expand...

??
Aside from the fact that the syntax [WS1],[WS2] doesn't work, assuming
the value from I15 was in a cell other than E41 on WS2, how would the
user ever tell whether the value from I15 was found on WS1 or on WS2???

Alan Beban
Click to expand...
 
Earl, Alan: Thank you both for your help thus far. All I want to
accomplish is to be able to 'tell' a formula that in a parameter the
data I want taken into consideration is from two worksheets. Instead of
saying, for instance,

=MATCH(I15,Jones!E5:E40,FALSE)

I want the second parameter to take data from, let's say, five
contigious worksheets, into consideration. This is more of an
array-type question than a "MATCH" question. I still haven't figured it
out, and to be quite honest, I can't believe it would be this difficult.

Any further assistance is greatly appreciated, and thanks for your prior
efforts.

** Posted via: http://www.ozgrid.com
Excel Templates, Training, Add-ins & Business Software Galore!
Free Excel Forum http://www.ozgrid.com/forum ***
 
Bubbis,

Since you're looking up data in multiple worksheets, it may well be that you
should have all the data in one sheet with an extra column to make whatever
the sheet distinction was. All kinds of powerful Excel tools are now
available (not to mention VLOOKUPs). An Autofilter can quickly reduce it
to the equivalent of an original sheet. Strongly recommend you consider
this.
 
Well, I repeat: What do you expect a function to return if the value
from I15 is in E7 of WS1? And what do you expect it to return if the
value from I15 is instead in E7 of WS2?

Alan Beban
 
Back
Top