| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Returning Numeric Results across a Single Row in Consecutive Cells
Sorry Sam,
I completely misundersttod what you wanted. My formula could be adapted to work, but as you have a good solution from Domenic, it is hardly worth it. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Sam via OfficeKB.com" <u4102@uwe wrote in message news:634bd42b74889@uwe... Hi Bob, Bob Phillips wrote: Just adjust the range being returned, after the INDEX Not sure what to adjust the range to? Use this array formula =IF(ISERROR(SMALL(IF((ISNUMBER(MATCH(D3:Z3,$D$6:$Z $6,0)))+(ISNUMBER(MATCH(D3 :Z3,$D$16:$Z$16,0))),COLUMN($D$3:$Z$3)-MIN(COLUMN($D$3:$Z$3))+1,""),TRANSPOS E(ROW($A1:$A20)))),"", INDEX(D3:Z3,1,SMALL(IF((ISNUMBER(MATCH(D3:Z3,$D$6: $Z$6,0)))+(ISNUMBER(MATCH( D3:Z3,$D$16:$Z$16,0))),COLUMN($D$3:$Z$3)-MIN(COLUMN($D$3:$Z$3))+1,""),TRANSP OSE(ROW($A1:$A20))))) Cheers, Sam -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200607/1 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Returning Numeric Results across a Single Row in Consecutive Cells
Hi Bob,
No problem. Cheers, Sam Bob Phillips wrote: Sorry Sam, I completely misundersttod what you wanted. My formula could be adapted to work, but as you have a good solution from Domenic, it is hardly worth it. Hi Bob, [quoted text clipped - 3 lines] Use this array formula =IF(ISERROR(SMALL(IF((ISNUMBER(MATCH(D3:Z3,$D$6:$ Z$6,0)))+(ISNUMBER(MATCH(D3 :Z3,$D$16:$Z$16,0))),COLUMN($D$3:$Z$3)-MIN(COLUMN($D$3:$Z$3))+1,""),TRANSPOS E(ROW($A1:$A20)))),"", INDEX(D3:Z3,1,SMALL(IF((ISNUMBER(MATCH(D3:Z3,$D$6 :$Z$6,0)))+(ISNUMBER(MATCH( D3:Z3,$D$16:$Z$16,0))),COLUMN($D$3:$Z$3)-MIN(COLUMN($D$3:$Z$3))+1,""),TRANSP OSE(ROW($A1:$A20))))) Cheers, Sam -- Message posted via http://www.officekb.com |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Match 3 Criteria and Return Lowest Numeric Value | Excel Worksheet Functions | |||
| abdualmohsn | Excel Discussion (Misc queries) | |||
| How do i keep cells with a formula returning "" from being plotted | Charts and Charting in Excel | |||
| Count Consecutive Cells | Excel Discussion (Misc queries) | |||
| FILL DATES IN VARIOUS CELLS BASED ON A DATE ENTERED IN A SINGLE C. | Excel Discussion (Misc queries) | |||
Hybrid Mode
