Finding start and end columns using MATCH function

[Pedsdude]

I'll get straight down to business with an explanation of what I'm trying to achieve ...



I have a table as follows:
\1 - note: cells containing times and temperatures are numeric values, I've used custom formatting to add " mins" and "°C" on the end

In one cell, a user can input a time period (in mins), and in another cell, I would like to output the start and end columns (in letter/s) for the chosen time.

For example, in the link above, if the value inputted '30' then the start column is 'C' and the end column is 'G'. If the user had inputted '60', then the results would be 'H' and 'K', etc.


I am able to find the start column with the following equation:
=SUBSTITUTE(ADDRESS(1,MATCH(VALUE,C2:P2,0)+2,4),"1","")


I am struggling, however, to find the end column for the chosen time.

If each time had the same number of temperatures then it would be a simple case of adding the number of temperatures per time, minus 1, after the "+2".

My first idea was to copy the above function, but to change the "0" in the MATCH function to "-1", in order to find the next greatest time value. However, it appears that using "-1" in the MATCH function only works if the range is in descending order - my times are always in ascending order.

I have a further problem in that the time periods are not always the same values (i.e. it won't always be 30-60-90-120, it could for example be 30-45-60-75). So as far as I know, there is no way in advance of knowing the next time period up (and then subsequently taking one column to the left).



I think I am capable of finding a solution, but not without it being extremely messy.

One possible workaround I have considered involves the following:
- Add a new row between rows 2 and 3, which has the time values in each cell (i.e. from column C, values of 30-30-30-30-30-60-60-60-60-90-90-90-120-120) - effectively duplicating the time row, but not merging cells
- Use the COUNTIF function to find the number of cells in that row containing the chosen time
- Add this value, minus 1, after the "+2" in the above formula, i.e. =SUBSTITUTE(ADDRESS(1,MATCH(VALUE,C2:P2,0)+2+COUNTIF(C3:P3,"="&VALUE")-1,4),"1","")

Ideally, however, I would like to avoid having to add a new row, because it's not a particularly 'clean' solution, and I have quite a lot of these tables!



Any ideas would be greatly appreciated!

[Pepe Le Mokko]

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

I would also recommend removing any merged cells from your book which will most certainly provide problems as usual

[Pedsdude]

Thank you - I have edited my original post to include an example Workbook.

[XOR LX]

Hi,

Ah the old merged cells again and the problems they cause!

Perhaps:

=SUBSTITUTE(ADDRESS(1,IFERROR(1+MATCH(TRUE,INDEX(C2:P2>E11,,),0),LOOKUP(7^7,COLUMN(C2:P2))),4),"1","")

Regards

[Pedsdude]

Excellent, that worked perfectly, thank you very much!

I've had a look through and understand the reasoning behind it

One quick question - what's the reasoning behind using the LOOKUP function, and why 7^7 specifically? Is this the total amount of permitted columns in an Excel worksheet, or just an incredibly large number you've picked out at random? If I already know what the final column is, presumably I could just replace the LOOKUP function with COLUMN(P1)?

[XOR LX]

You're welcome!

"any reason why you've used 7^7 specifically? Is this the total amount of permitted columns in an Excel worksheet, or just an incredibly large number you've picked out at random?"

The latter - technically as long as it's greater than 16384 (the number of columns in Excel 2010) then there won't be any issues, though my choice is quite arbitrary!

Regards

[Pedsdude]

Apologies, it seems I edited my post while you were already replying, so you missed my question re: using COLUMN(P1) instead!

[Pete_UK]

Here's a simpler way of getting the start column, in E13:

=CHAR(64+MATCH(E11,2:2,0))

and in E14 you can have this:

=CHAR(64+MATCH(E11+30,2:2,0)-1)

though for this to work fully you will also need to add the next number in sequence (i.e. 150) to cell Q2 - you can colour this white so it is not visible, if you don't want to see it on the sheet.

Hope this helps.

Pete

[XOR LX]

"Apologies, it seems I edited my post while you were already replying, so you missed my question re: using COLUMN(P1) instead!"

Yes you could. I was just making the formula as dynamic as possible, though your way would be technically more efficient as it involves less function calls.

You seem to have a good intuitive grasp of Excel, by the way.

Regards

[XOR LX]

@Pete_UK

Doesn't a solution such as that assume that the difference in times will always be constant?

And what if the table extends to column AA and beyond?

Regards

[Pete_UK]

I can only go off what is presented. It the increments vary then it is easy enough to put the increment in A1, say, and change the 30 in the second formula to refer to A1.

Pete

[XOR LX]

You misunderstood. If the increment is not constant then putting it in a cell will not help matters...

Regards

[Pete_UK]

Well, what is there in the OP's attachment or in the lengthy description that suggests the increment is not constant? In this table it is 30, in another table it might be 15, or 10, say, but whatever it is, the numbers are likely to increase by the same amount I would have thought.

Pete

[Pedsdude]

Here's a simpler way of getting the start column, in E13:

=CHAR(64+MATCH(E11,2:2,0))

and in E14 you can have this:

=CHAR(64+MATCH(E11+30,2:2,0)-1)

though for this to work fully you will also need to add the next number in sequence (i.e. 150) to cell Q2 - you can colour this white so it is not visible, if you don't want to see it on the sheet.

Yep, unfortunately I do not know what the next number in the sequence is, and I have a significant number of these tables in my Workbook.

Your formula works fine for columns up to Z, after column Z it starts providing lowercase solutions (e.g. it will give "b" instead of "AH") - is there a way to resolve this?

EDIT: Just seen all the replies since I started constructing this message. The time intervals will not necessarily be constant, i.e. it could be 30-45-60-120-240, etc.

Yes you could. I was just making the formula as dynamic as possible, though your way would be technically more efficient as it involves less function calls.

You seem to have a good intuitive grasp of Excel, by the way.

Thank you! I spend a significant portion of my time at work on Excel, so I like to think I'm pretty capable

I'm also aware of the importance of speed of calculation (particularly in the document I'm working on), and so while I may be able to generate a solution by myself, it's normally better to get the most efficient solution from someone more knowledgeable!

[XOR LX]

"Thank you! I spend a significant portion of my time at work on Excel, so I like to think I'm pretty capable."

Then you should know better than to use merged cells!!!

[Pedsdude]

But I'm a perfectionist when it comes to appearance!

[XOR LX]

Ah, I see!

Well, at least it makes the formula work a touch more interesting!

[Pete_UK]

Your formula works fine for columns up to Z, after column Z it starts providing lowercase solutions (e.g. it will give "b" instead of "AH") - is there a way to resolve this?

If you are likely to have colunms beyond Z, you can put this in E13:

=IF(MATCH(E11,2:2,0)>26,CHAR(64+INT(MATCH(E11,2:2,0)/26)),"")&CHAR(64+MOD(MATCH(E11,2:2,0),26))

and this in E14:

=IF(MATCH(E11+30,2:2,0)-1>26,CHAR(64+INT((MATCH(E11+30,2:2,0)-1)/26)),"")&CHAR(64+MOD(MATCH(E11+30,2:2,0)-1,26))

Both will return up to ZZ. Note that it still relies on an equal increment (30 in this case), and relies on you putting the last number +30 in the next available column, as before (quicker than inserting another row with all the increments in, as you were proposing).

Anyway, just another way of doing it, but it would help if you spelled out all the pertinent details to begin with.

Hope this helps.

Pete