<<Convert Excel Spreadsheets to Web Pages \| Trading Software That Operates Within Excel \| Convert Excel, Access & Other Databases \| Merge Excel Files>>

Excel and Lookup Functions. How to Find the nth Occurrence

| | Information Helpful? Why Not Donate.

TRY OUT: Smart-VBA | Code-VBA | Analyzer-XL | Downloader-XL | Trader-XL| More Free Downloads.. Best Value: Finance Templates Bundle

Current Special! Complete Excel Excel Training Course for Excel 97 - Excel 2003, only ~~$145.00~~. $59.95 Instant Buy/Download,30 Day Money Back Guarantee & Free Excel Help for LIFE!

Got any Excel Questions? Free Excel Help

SEE ALSO:Excel Lookup Assistant ||Excel VLOOKUP Explained || Excel Add-ins Over 200!

Microsoft Excel has a very rich selection of Lookup & Reference Functions. These can be used on a Table of data to extract out details corresponding to a specified value. Perhaps the most popular of these Excel functions is theVLOOKUP Formula .

If we combine (Nesting) Lookup functions together we can do some pretty clever stuff, but unfortunately Excel has no function that will return the nth occurrence of our specified data. There is however a very simple method we can use to find any specified occurrence we like.

Assume we have a two column table of data, with Column A housing first names and Column B their corresponding ages. We can easily use aVLOOKUP Formula to extract out a persons age based on their name. The trouble is there is more the one occurrence of some names. We do not want to lookup the name Dave and have theVLOOKUP Formula find the first occurrence, but the 2nd, 3rd or whatever. Here is how we can do this. Remember this example is based on the data being in Column A and B.

First select all of Column A by clicking the letter A, then go to Insert>Columns, now Starting from cell A2 (B1 is a heading) enter this formula:

=B2&COUNTIF($B$2:B2,B2)

Now copy this down as many rows as you have data in Column B. You will end up with names like: Dave1,Dave2, Dave3 etc. If you haven't guessed already, we can now use Column A as the column to find the nth occurrence of any name. If the Lookup function we were using is the VLOOKUP, then the formula below will return the age for the 3rd occurrence of the name Dave.

=VLOOKUP("Dave3",$A$1:$C$100,3,FALSE)

You can of course hide Column A from view as we do not need to see it. You can even use the names in Column A as the Source range for a list in another cell by going toData>Validation -ListThen reference the cell housing this list in your VLOOKUP

Excel Dashboard Reports & Excel Dashboard Charts 50% Off Become an ExcelUser Affiliate & Earn Money

Special! Free Choice of Complete Excel Training Course OR Excel Add-ins Collection on all purchases totaling over $64.00. ALLpurchases totaling over $150.00 gets you BOTH! Purchases MUST be made via this site. Send payment proof to [email protected] 31 days after purchase date.

Instant Download and Money Back Guarantee on Most Software

Try out:Analyzer XL |Downloader XL |Smart VBA |Trader XL Pro (best value) |ConsoXL | MergeXL | O2OLAP for Excel | MORE>>

Excel Trader PackageTechnical Analysis in Excel With $139.00 of FREE software!

Microsoft � and Microsoft Excel � are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft