"General" wrote in news:1129835807.668986.77450
@g43g2000cwa.googlegroups.com:

Confusing subject I know.
I have to process data that looks like this:

A1: GE
A2: (empty)
A3: Q104
A4: Q204
A5: Q304
A6: (empty)
A7: Ford
A8: Q104
A9: Q304
A10: (empty)
A11: (empty)
A12: AOL
etc..

Given a company name (GE, Ford, AOL, et...) and a quarter (Q104, Q204,
etc..), I need to be able to return the row number for that quarter for
that company.

I can't use a simple match function because the quarters are repeated -
for example, Q104 shows up in both GE and Ford. For Ford Q104 I need to
return 8, while for GE Q304, I need to return 5.

Constraints:
1)The quarters will be in sequential order, earliest to latest.
2)Some companies are missing quarters, and I won't know ahead of time
how many companies each quarter has.
3) The data will be replaced frequently, sometimes companies will just
have a few quarters, sometimes they will go back 10 years
4) Some companies will be missing quarters. If this happens, then the
next available quarter will be in that row (look at row 9 above)
5) Because of (3), I can't use names - I can't make new names for each
company whenever the user updates the sheet.
6) I want to do this with spreadsheet functions only - no macros
(stupid company policy).

I just need to return the row number, using only worksheet functions -
no macros.

Please help!!!

Phil





Phil,

It could probably be done if you knew the company order:

Search for the company in question to get the first row
Search for the next company in the list to get the last row
Then search for the quarter between these rows


However, I would suggest that you change the structure of the data.
Company and Quarter should be separate columns:

GE Q104
GE Q204
GE Q204
..
..
..


If D1="GE" and D2="Q204" then you could then use something like:

{=MATCH(D1&D2,A1:A10&B1:B10,0)}

to return the position. Note that this is an array formula, use
Control+Shift+Enter instead of just Enter when typing the formula. Excel
will add the brackets.

Is there anything useful in the empty rows?


HTH,
RP