98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Excel Training and help » Lookup first occurance of number
Lookup first occurance of number
Resolved · Medium Priority · Version 2010
Sun 10 Mar 2013, 16:36Delegate Simon said...
Simon has attended:
Excel Intermediate course
Excel Advanced course
Excel Advanced - Formulas & Functions course
Excel VBA Intro Intermediate course
Excel VBA Advanced course
Excel Advanced - For Power Users course
Lookup first occurance of number
Hi, I would be grateful for some help.
I have a column of numbers and would like to count the number of rows before a specific number (e.g. 38.8) has its first occurrence. The context to this is the amount of time it takes a person to get to a certain temperature if each row is 1 min.
Many thanks
Simon
Sun 10 Mar 2013, 19:38Trainer Gary said...
RE: Lookup first occurance of number
Hi Simon
Thanks for getting in touch.
The simplest option is MATCH. Here's an example:
=MATCH(38.8, A1:A50, 0)
Which will find the value "38.8" in the range of cells A1:A50. The zero ensures an exact match. The formula will return the answer in the form of a number. This number will count how many rows down the range the answer was found.
I hope this helps.
Kind regards
Gary Fenn
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Sun 10 Mar 2013, 19:51Delegate Simon said...
RE: Lookup first occurance of number
Thanks Gary, that works great. If there is not a 38.8 in the range, I get an N/A error what would I need to display the total rows, an if function?
Sun 10 Mar 2013, 20:00Trainer Gary said...
RE: Lookup first occurance of number
Hi Simon
Try the always-useful IFERROR:
=IFERROR(MATCH(38.8, A1:A50, 0),COUNTA(A1:A150))
If the function returns an error, instead count how many entries are in the range and return that instead.
Kind regards
Gary Fenn
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Sun 10 Mar 2013, 20:09Delegate Simon said...
RE: Lookup first occurance of number
That works great, but I think the iferror function was developed for the 2010 version. I need it to be compatible with 2003 for work. Sorry didnt mention that, is there an alternative?
Sun 10 Mar 2013, 20:14
Edited on Sun 10 Mar 2013, 20:15Trainer Gary said...
RE: Lookup first occurance of number
Hi Simon
Possible - just a little torturous:
=IF(ISERROR(MATCH(38.8, A1:A50, 0)), COUNTA(A1:A150), MATCH(38.8, A1:A50, 0))
You can see why IFERROR was invented!
Kind regards
Gary Fenn
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Training information:
See also:
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Filtering Data in an Excel 2010 WorksheetWhen you have an Excel Worksheet with masses of data, it's not going to be easy to sift through it. So, in order to view sections of data, you can use the filter tool. Select the cells you want to filter (no need to select the column headers), then click the Home tab on the Ribbon, click Sort and Filter (you will find this in the Editing Section) then click Filter. |
