Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup value then return result from other column, varying row num
Hello,
I have used the excel discussion group to solve many problems, and now have one that I need to post! I have a report that exports data (actually in text format, but that may not matter) where I need to look down column A and find the cells with "AxxxS" where the xxx are numbers (well, text) in ascending order. In between the "AxxxS" values are cells in column a with various meaningless text data. What I need to find is the maximum value (remember these numbers are exported to this file as text) in column H that is some rows down from the "AxxxS" row. The number of rows down can vary, but it always will be the cell with one row above the next "AxxxS" value. There are over 1000 rows, so I have to pull around 100 values of "AxxxS" and the corresponding number in column H. Also, the number of rows between each "AxxxS" can be as little as two rows, or 15 rows. Lastly, there are other values in column H, but the one I need is the maximum of those values before the next "AxxxS" sequence. I have been trying sumproduct and offset and other combinations, but can't find a solution, hopefully someone can help! Here is what the data looks like. A B --- H 1 A500S 2 othertext 3 othertext 4 othertext 3838 5 A501S 6 othertext 7 othertext 2500 8 A502S 9 othertext 10 othertext 11 othertext 3000 12 A503S 13 othertext 14 othertext 15 othertext 16 othertext 1000 17 A504S 18 othertext 19 othertext 20 othertext 1200 Want to return in columns J and K: A500S 3838 A501S 2500 A502S 3000 A503S 1000 A504S 1200 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
lookup for a value in multiple columns and return a result | Excel Discussion (Misc queries) | |||
Lookup 3 columns and return a result from another column | Excel Discussion (Misc queries) | |||
LOOKUP and return the column heading for IF/THEN return for False | Excel Discussion (Misc queries) | |||
lookup value and return result in column to left | Excel Worksheet Functions | |||
I need a Lookup to return more than 1 result | Excel Worksheet Functions |