Hevitra ato Anatiny
All classic search and type substitution functions VPR (VLOOKUP), GPR (HLOOKUP), MISEHO BETSAKA (MATCH) and those like them have one important feature – they search from the beginning to the end, i.e. left-to-right or top-to-bottom in the source data. As soon as the first matching match is found, the search stops and only the first occurrence of the element we need is found.
What to do if we need to find not the first, but the last occurrence? For example, the last transaction for the client, the last payment, the most recent order, etc.?
Method 1: Finding the Last Row with an Array Formula
If the original table does not have a column with a date or a serial number of a row (order, payment …), then our task is, in fact, to find the last row that satisfies the given condition. This can be done with the following array formula:
Eto:
- asa IF (RAHA) checks all cells in a column one by one Customer and displays the line number if it contains the name we need. The line number on the sheet is given to us by the function LINE (ROW), but since we need the row number in the table, we additionally have to subtract 1, because we have a header in the table.
- Avy eo ny asa MAX (MAX) selects the maximum value from the formed set of row numbers, i.e. the number of the most recent line of the client.
- asa INDEX (INDEX) returns the contents of the cell with the found last number from any other required table column (Kaody kaody).
All this must be entered as formule array, i.e.:
- In Office 365 with the latest updates installed and support for dynamic arrays, you can simply press Midira.
- In all other versions, after entering the formula, you will have to press the keyboard shortcut Ctrl+fanovana+Midira, which will automatically add curly braces to it in the formula bar.
Method 2: Reverse lookup with the new LOOKUP function
I already wrote a long article with a video about a new feature VIEW (XLOOKUP), which appeared in the latest versions of Office to replace the old VLOOKUP (VLOOKUP). With the help of BROWSE, our task is solved quite elementarily, because. for this function (unlike VLOOKUP), you can explicitly set the search direction: top-down or bottom-up – its last argument (-1) is responsible for this:
Method 3. Search for a string with the latest date
If in the source data we have a column with a serial number or a date that plays a similar role, then the task is modified – we need to find not the last (lowest) line with a match, but the line with the latest (maximum) date.
I have already discussed in detail how to do this using classic functions, and now let’s try to use the power of the new dynamic array functions. For greater beauty and convenience, we also convert the original table into a “smart” table using a keyboard shortcut Ctrl+T na baiko Trano - Atao ho latabatra (Horonan-trano - endrika toy ny latabatra).
With their help, this “killer couple” solves our problem very gracefully:
Eto:
- Function first FILTER (FILTER) selects only those rows from our table where in the column Customer – the name we need.
- Avy eo ny asa Grade (SORT) sorts the selected rows by date in descending order, with the most recent deal at the top.
- asa INDEX (INDEX) extracts the first row, i.e. returns the last trade we need.
- And, finally, the external FILTER function removes the extra 1st and 3rd columns from the results (Kaody kaody и Customer) and leaves only the date and amount. For this, an array of constants is used. {0;1;0;1}, defining which columns we want (1) or do not want (0) to display.
Method 4: Finding the Last Match in Power Query
Well, for the sake of completeness, let’s look at a solution to our reverse search problem using the Power Query add-in. With her help, everything is solved very quickly and beautifully.
1. Let’s convert our original table into a “smart” one using a keyboard shortcut Ctrl+T na baiko Trano - Atao ho latabatra (Horonan-trano - endrika toy ny latabatra).
2. Load it into Power Query with the button Avy amin'ny Table/Range vakizoro Data (Angona — Avy amin'ny Tabilao/Tabilao).
3. We sort (through the drop-down list of the filter in the header) our table in descending order of date, so that the most recent transactions are on top.
4… Ao amin'ny tabilao fiovana mifidy ekipa Vondrona amin'ny (Transform — Vondrona araka) and set the grouping by customers, and as an aggregating function, select the option Andalana rehetra (Ny andalana rehetra). You can name the new column anything you like – for example Details.
After grouping, we will get a list of unique names of our clients and in the column Details – tables with all the transactions of each of them, where the first line will be the latest transaction, which is what we need:
5. Add a new calculated column with the button Tsanganana manokana vakizoro Manampia tsanganana (Add column — Add custom column)ary ampidiro ity formula manaraka ity:
eto Details – this is the column from which we take tables by customers, and 0 {} is the number of the row we want to extract (row numbering in Power Query starts from zero). We get a column with records (Record), where each entry is the first row from each table:
It remains to expand the contents of all records with the button with double arrows in the column header Last deal selecting the desired columns:
… and then delete the column that is no longer needed Details by right-clicking on its title – Esory ny tsanganana (Remove columns).
After uploading the results to the sheet through Home — Akatona sy asio entana — Akatona ary ampidiro ao (Ao an-trano — Akatona & Ampidiro — Akatona & Ampidiro amin’ny…) we will get such a nice table with a list of recent transactions, as we wanted:
When you change the source data, you must not forget to update the results by right-clicking on them – the command Fanavaozana & Tehirizo (Havaozina) na hitsin-dàlana fitendry Ctrl+Alt+F5.
- The LOOKUP function is a descendant of VLOOKUP
- How to use the new dynamic array functions SORT, FILTER, and UNIC
- Mitady ny sela tsy misy banga farany amin'ny andalana na tsanganana misy ny fiasa LOOKUP