Possible array formula?
"Harlan Grove" wrote in message
...
"Jason" wrote...
ID Side Size Type Price OCA Status
2345254 Buy 1 Lmt 103.3125 OCA Open
....
2345266 Buy 3 Lmt 103.28125 Open
...
With this data table how in excel can I find the record
with the lowest ID with a price of 103.53125 and a status of open?
If you data were in A1:G14, and if the table were sorted on ID in
ascending
order already, the following array formula will give the ID number you
seek.
=INDEX(A2:A14,MATCH(1,(E2:E14=A17)*(G2:G14=B17),0) )
If the table weren't sorted on ID, try the array formula
=MIN(IF((E2:E14=A17)*(G2:G14=B17),A2:A14))
Out of force of habit, I would use
=MIN(IF(E2:E14&G2:G14=A17&B17,A2:A14))
Mine has fewer comparisons to make, but I would imagine
the number to text conversion is inefficient.
For something this small, typing it in takes more time then
calc'ing it many, many times, so I wouldn't care. But I have
some spreadsheets that may have a few hundred array
formulas covering several thousand rows of data.
Are there any tests/studies of excel performance for various
functions and/or formula structure?
KenC
|