Category: Lookup and References

Lookup and references functions, comes with Excel

Walking Columns

January 14, 2023 ANmar

An easy way to scroll through set of data in a graphic way without graphs.

A-pure-formula xlsx workbook. These were done using formulas, conditional formatting, and ActiveX control.

Get 3rd Wednesday of month

November 15, 2022 ANmar

This was an interesting request.

Needing to get the date of 3rd Wednesday for a given month, automatically, using pure Excel functions.

So, sharing here on what is the formula and how to get there.

Basic Math, CHOOSE, DATE, Date and Time, Formulas - combined functions, Lookup and References, MONTH, Standard functions, WEEKDAY, YEAR

Repeated Offset

September 19, 2022 ANmar

I needed to have an offset of columns to be repeated every 12 months.

This is part of a project, we needed to calculate the coefficient of sales prediction based on the month number, we do have coefficient saved in another table that repeated every year (12 months).

Sorting with functions only

September 5, 2022 ANmar

This one is similar to http://xlfxs.com/sort-list-using-functions/ where we use some controls here that make it more user-friendly.

CHOOSE, Formulas - combined functions, LARGE, Lookup and References, MATCH, Math and Trig, ROW, SMALL, Standard functions, VLOOKUP

Searching table in 2 dimensions.

July 26, 2022 ANmar

Using VLOOKUP + MATCH (HLOOKUP + MATCH, OFFSET + 2 MATCHes or INDEX + 2 MATCHes) to search a table in both axis.

This post has been sitting for a while in my archive, waiting for me to get some time to polish and post.

Formulas - combined functions, HLOOKUP, INDEX, Lookup and References, MATCH, Standard functions, VLOOKUP

Search across sheets in function

July 22, 2022 ANmar

Just when you need to search for a thing across sheets in a workbook.

I am bored, so I am adding articles from my old archive.

ADDRESS, COUNTIF, Formulas - combined functions, INDIRECT, Lookup and References, OFFSET, Standard functions, Statistical

Min Max vehicle

July 20, 2022 ANmar

Finding which vehicle with minimum (or maximum) amount of savings among peers.

This is an old request, found its formula in my archive to calculate which of the 4 columns is actually has the minimum (or maximum) of savings among other columns.

Trick is as you guessed it was OFFSET.

GoLast to jump to last cell in a column

July 20, 2020 ANmar

Edit 2021-07-12: I needed this formula to work in a worksheet having space in its name, so original one did not work, here is the new version: =HYPERLINK(“[“&MID(CELL(“filename”,A1),SEARCH(“[“,CELL(“filename”,A1))+1,SEARCH(“]”,CELL(“filename”,A1))-SEARCH(“[“,CELL(“filename”,A1))-1)&”]'”&MID(CELL(“filename”,A1),SEARCH(“]”,CELL(“filename”,A1))+1,500)&”‘!B”&COUNT(B:B)+6,”Go Last”) Again, above formula works in a worksheet with space in its name (and any other character), it already works in a workbook with space in its…

CELL, COUNT, Formulas - combined functions, HYPERLINK, Information, Lookup and References, Standard functions

CHOOSE – tree of decisions

February 20, 2020 ANmar

If you are like me, then you must have needed to use CHOOSE functionThis is basically like “SELECT CASE” statement, but more limited So, in few words, you can derive the flow of formulas to go to function1, function2, function3, or function4 based on an integer.More like a nested IF, it can actually replaces a…

CHOOSE, Formulas - combined functions, IF

Looplist – Repeating months using formulas

December 11, 2018 ANmar

Using formulas to repeat list, I call it LoopList

Used to list Months for several years, in away that with 1 formula, it will loop through all months, then jumps into next year and loop through its months again, and so on