LinkBack Thread Tools Search this Thread Display Modes
Prev Previous Post   Next Post Next
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 6
Default Find the last row using 2 to 20 criteria

The worksheet looks like this:

Column A Column B Column C

1 3 21
5 8 26
16 21 37
1 19 49
2 37 41
3 21 51

The actual worksheet features 23 columns like the ones depicted above (A,
B, C),
and also features 205,000 rows of data.
However, you may want to answer only in regards to the smaller table
found above-and we'll take it from there.

The question:
How does one find the last row featuring say, the numbers 3 & 21?
In the example above, the last row is row 6.

Note the following:
Whatever formula is to be used CANNOT use a single column as its
reference. This because, any number can appear in any column.
Ex: The number 3 may appear in either column A, B or C (and actually in
another 21 columns of the actual worksheet).
Also, instead of finding a row featuring only 2 numbers (Ex: numbers 3
& 12), there needs to be a way of also finding the LAST row featuring up to
12 chosen numbers. Ex: Find which is the last row which contains the
following numbers:
1, 2, 3, 4, 5, 6, 7, 8, 10, 12, 14, 16.
Lastly, we are using Office 2007 (using multiple cores) and a very
powerful, quad-core cpu; this, in reference to the power demand of using such
array formulas...
Any assistance will be greatly appreciated.

P.S. The closest we've come to the answer looks like this (Array formula):
=MAX(IF((A1:A6=3)*(A1:C6=21),ROW(A1:C6),"")).

This formula would work if the number 3 appeared only under column A...

HOWEVER: This formula will not work because the reference to the first #
(number 3) is made only in regards to column A. Instead, it should've refered
to A1:C6-which is impossible in this formula.
 
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Find MAX number with criteria GoBow777 Excel Worksheet Functions 5 May 17th 09 09:52 PM
EXCEL - Meet 2 criteria, then find next case of third criteria Elaine Excel Worksheet Functions 3 December 1st 08 10:43 PM
Where is "open/tools/find/find files that match these criteria"? PJ Excel Discussion (Misc queries) 2 November 14th 08 04:11 PM
find with six different criteria damorrison Excel Discussion (Misc queries) 8 November 18th 06 05:52 PM
Trying to find a max value with criteria slot guy Excel Worksheet Functions 1 March 11th 05 04:23 PM


All times are GMT +1. The time now is 12:10 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"