Count - index - match problem

syedbokhari

Board Regular
Joined
Aug 19, 2013
Messages
94
Hey Chaps

I am basically trying to retrieve a date in column F with specific conditions.

Basically I want to get the result (DATE) from after the DATE in cell E6 and E7.

E.g

Therefore CELL F6 should show 03/12/2012 because CELL E6 has the date of 26/11/2012 (<--- Hence M5[Date] Onwards).

And F6 should show 06/11/2012 but instead shows 01/11/2012.

My current formula pulls the very first date but what I want is the value/date onwards from cell E6-E7.

REALLY SORRY ABOUT THE PICTURE ... I can't install HTML Genie at work so had to upload a photo!

tinypic.com


Again!

SUPER APPRECIATIVE FOR ANY HELP OR GUIDANCE! Will donate to charity for your choice for your help!

Kind regards

Syed from England
 
Last edited:

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Syed,
Maybe ........


Excel 2007
FGHIJKLMNO
5Use Date (WC)01/11/201203/11/201202/11/201206/11/201226/11/201203/12/201210/12/2012
603/12/201285
706/11/201242
Sheet1
Cell Formulas
RangeFormula
F6=LOOKUP(9.99E+307,I6:AZ6,$I$5:$AZ$5)



Hope that helps.
 
Upvote 0
Hey Snake!

Thanks for the response but I keep getting the last value.

What I want is the date in cell F6 and F7 after the Date in E6 and E7.

So simply put I want the first date after the date in E6 and E7.

tinypic.com
 
Upvote 0
Tony has indeed provided a formula that should return the last numeric value from the reference of interest.
If that's naot what you are after: What is the outcome you want to see?
 
Upvote 0
Hey Aladin!

I don't want the last value ... nor the first value!

I require the "First" value after the Bill date and not the first value in the row.

I had this formula giving me the first value but in the row

=IF(COUNT($J105:$CA105),INDEX($I$5:$CA$5,MATCH(TRUE,INDEX(ISNUMBER($J105:$CA105),0),0)),"No Usage")

However like I said before Aladin I now need the first value from the specified date onwards.


You chaps helped me before getting the first value however my boss has changed her mind and what's the first value after the "bill date".

http://www.mrexcel.com/forum/excel-questions/767866-formula-returns-horizontal-field-names.html

Again!

Super GRATEFUL CHAP!
 
Last edited:
Upvote 0
Hey Aladin!

I don't want the last value ... nor the first value!

I require the "First" value after the Bill date and not the first value in the row.

I had this formula giving me the first value but in the row

=IF(COUNT($J105:$CA105),INDEX($I$5:$CA$5,MATCH(TRUE,INDEX(ISNUMBER($J105:$CA105),0),0)),"No Usage")

However like I said before Aladin I now need the first value from the specified date onwards.


You chaps helped me before getting the first value however my boss has changed her mind and what's the first value after the "bill date".

http://www.mrexcel.com/forum/excel-questions/767866-formula-returns-horizontal-field-names.html

Again!

Super GRATEFUL CHAP!

But, what is the outcome you want to see, regarding Elaine for example?
 
Upvote 0
Syed,

Then perhaps ....

Excel 2007
EFGHIJKLMNOPQR
5Bill DateUse Date (WC)01/11/201203/11/201202/11/201206/11/201226/11/201203/12/201210/12/201211/12/201212/12/201213/12/2012
626/11/201203/12/2012857
702/11/201206/11/2012426
Sheet1
Cell Formulas
RangeFormula
F6=IFERROR(INDEX(OFFSET(I$5:CA$5,0,MATCH(E6,I$5:CA$5,0)),MATCH(TRUE,INDEX(ISNUMBER(OFFSET(I6:CA6,0,MATCH(E6,I$5:CA$5,0))),0),0)),"No Usage")
 
Last edited:
Upvote 0
Hey Aladdin and Tony!

I am at home now so I have access to Excel Geanie!
I just took the work sheet I was working at work home and changed the Account Names to ex GF's :D

Basically as you can see with Elaine and Lidiane I am getting the first date return to me. My formula currently goes along just finds the first value and gives me the date for it. As of yesterday my boss wants the value (date) from the bill date onwards as the case with Mia and Juliets entries .

Hope this helps!

Excel Workbook
DEFGHIJKLMNOPQRSTUVWXYZ
4
5AccountNumberAccountNamePractice AreaBill DateUse Date (wc)Bill to Use26/11/1203/12/1210/12/1217/12/1224/12/1231/12/1207/01/1314/01/1321/01/1328/01/1304/02/1311/02/1318/02/1325/02/1304/03/1311/03/1318/03/13
6ELAINEChinese and CoInvestment31/12/1210/12/201257744442
7LIDIANEBrazil PartnersDesigner21/01/201326/11/2012111111111
8MIAChelsea HarbourFamily17/12/201224/12/20127844
9JULIETFontaine SolicitorsLuxury3/12/201217/12/2012111
10
11
12
13
14
15
16
17H6 =Elaine07/01/2013
18H7=Lidiane28/01/2013
19
Sheet1
 
Upvote 0
Hey Aladdin and Tony!

I am at home now so I have access to Excel Geanie!
I just took the work sheet I was working at work home and changed the Account Names to ex GF's :D
...

You seem still discussing what the earlier formulas return.

Can you tell what your boss want to see as result for Elaine? A result here means a concrete value...
 
Upvote 0

Forum statistics

Threads
1,213,567
Messages
6,114,342
Members
448,570
Latest member
rik81h

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top