index match help

[wilg]

Hi guys. Trying to do an index match to return a date.

I want to put a formula in cell ak11 which returns the date in row 8 if there is an "L" in row 10.

My colums in which it would be in is B to AF

There may be multiple instances of "L" but I want the last date in row 8 which would be closer to column AF


Let me know if I need to clarify further. Thanks again..

[mbarron]

Are there values other than the Ls in the range?
If no other values are in the range, you could use:
=INDEX(B10:AF10,MATCH("M",B8:AF8))

[wilg]

Hi, there will be other values in row 10 other than "L"

The formula seems to work. Would the formula you provided need to be modified due to other values like "F" or "PP" may be in same row as "L"?

[wilg]

Is there a way to return the latest result of "L" towards AF rather than the first instance?

[macropod]

=MAX(IF(B10:AF10="L",B8:AF8))
as an array formula will return the most recent 'matched' date. If the dates are sorted, then that will also be the last 'matched' date.

[wilg]

hmmm. I used your suggestion macropod which if I click in the fx button to check the formula it returns the datein the formula help. But in the cell it returns "#value!"

[macropod]

That just tells me you didn't input it as an array formula! Ctrl-Shift-Enter.

[wilg]

That's it....thanks so much.

[wilg]

What about the second most recent matched date? Is that possible?

[wilg]

My end result I need is to count how many instances of "L" between the two returned dates that the formula above gave me. I placed to returned result of the max formula in 2 separate cells to ref.

Can I now use this to ref the dates and in the range B12:af14 if countif "L" = 3 then 1?

in rows 8 and 12 are tha dates.

[wilg]

I have included an eg.

[macropod]

What about the second most recent matched date? Is that possible?
To get any series of values, you could use a 'LARGE' version of the formula instead:
=LARGE(IF(B10:AF10="L",B8:AF8),1) for the most recent
=LARGE(IF(B10:AF10="L",B8:AF8),2) for the 2nd most recent
=LARGE(IF(B10:AF10="L",B8:AF8),3) for the 3rd most recent
etc.
If you want the earliest date, you an use:
=MIN(IF(B10:AF10="L",B8:AF8))
or
=SMALL(IF(B10:AF10="L",B8:AF8),1)

To count how many 'L' characters appear between the earliest and latest date, the formula becomes:
=SUM(IF((B10:AF10="L")*(B8:AF8),1))
or even the ordinary formula:
=COUNTIF(B10:AF10,"L")

[wilg]

Hi Macropod, so I now know how to get return the date in row 8 for the second last instance of "L" in range B10:af10 and return the date from"B10:AF10 of the fist instance of "L" in B14:af14. (seem my posted workbook for visual.)

I now need to know how many instance of "L" between theses 2 dates.

I am storing the dates in ak14 and ak10, but do not know how to use a countif to ref the 2 dates in the range of B10:AF14


I hope I explaned this enough if not, please let me know. Thanks as always.

[wilg]

I don't know how the vba code would be but my theory would be like

if countif (range("ak10"):range("ak14"),"L")=3 then
msgbox "there are 3 instances of 3 between these 2 dates"

The range dates if there is an "L" would need to be included.

[macropod]

Well, if you know the 'L' you looked up on one row was the second-last one on that row, and the the 'L' you looked up on the next row was the first one on that row, logic dictates there can only be one 'L' between these two.

We have discussed in the past that your data are really poorly laid out for data analysis purposes. This would be so much easier if you organised the data in a way that is conducive to such analysis. To that end, see the attached revision to your workbook. You'll notice there's a new 'data' sheet, which uses formulae to pick up the data from your EG sheet. Thus arranged, the data are really easy to analyse. There's no need to worry about all the offsets etc that the EG sheet's layout entails - you can simply lookup any date range in column A on the Data sheet and extract whatever you want from column B.

I've also modified the EG sheet itself, using different formulae and conditional formatting rules to manage the layout. Adding a new month is now as simple as copying any previous month's rows and pasting them into the next available set of rows - then clearing out any unwanted data.

[wilg]

Hi, been working with suggested code from above but trying to incorperate 2 logic tests. But not successful, can you see anything I'm doing wrong to return the last instance of L or L3?

=LARGE(IF(OR(((B22:AF22="L")),(B22:AF22="L3")),B20:AF20),1)

[macropod]

What you might be doing wrong depends on what you're trying to achieve, and I don't know what that is.

Have you considered using the revised workbook structure I posted? If you did use it, all this stuff would be so much easier.

[wilg]

Hi Paul. I incorporated some of ur suggestion.

As for the formula i just posted, im trying to return the last result of "L" or "L3" from column b to af.

This is last piece of puzzle for me.

[wilg]

Hi guys, any suggestion on manipulating the Large If statement to return the last result of either L or L3? I tried 2 logics in the statement but that didn't work.

=LARGE(IF(OR(((B22:AF22="L")),(B22:AF22="L3")),B20:AF20),1)

[macropod]

If you use the 'data' sheet as per my previous post, the formula to return the last "L" or "L3" date can be as simple as:
=INDEX(Data!A:A,MATCH("L:",Data!B:B,1))

[wilg]

Hi Paul, with sincere appreciation and you suggestions above I was able to figure out the formula I needed for the last part of my workbook.

=IF(ISERROR(LARGE(IF(B14:AF14="L",B12:AF12,IF(B14:AF14="L3",B12:AF12)),1)),AR$4,LARGE(IF(B14:AF14="L",B12:AF12,IF(B14:AF14="L3",B12:AF12)),1))

I was not able to utilize you suggested data sheet as this workbook has over 250 sheets and to use a data sheet would be much dificult. But I have learned from your suggestions you put forth.

Thanks very much for your assistance.

Mike.