Mataupu
All classic search and type substitution functions VPR (VLOOKUP), GPR (HLOOKUP), TELE FA'AALIGA (FA'AVAE) 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:
Iinei:
- galuega tauave IF (AFAI) checks all cells in a column one by one tagata faʻatau 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.
- Ona fai ai lea 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.
- galuega tauave INDEX (INDEX) returns the contents of the cell with the found last number from any other required table column (Faʻatonuga numera).
All this must be entered as ituaiga fomu, i.e.:
- In Office 365 with the latest updates installed and support for dynamic arrays, you can simply press ulu.
- In all other versions, after entering the formula, you will have to press the keyboard shortcut ctrl+gāoioiga+ulu, 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 po o poloaiga Aiga – Fa'atulagaina e pei o se laulau (Aiga — Fa'atulagaina e pei o le Laulau).
With their help, this “killer couple” solves our problem very gracefully:
Iinei:
- Function first faʻamamā (FILTER) selects only those rows from our table where in the column tagata faʻatau – the name we need.
- Ona fai ai lea Vasega (FUAFUAGA) sorts the selected rows by date in descending order, with the most recent deal at the top.
- galuega tauave 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 (Faʻatonuga numera и tagata faʻatau) 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 po o poloaiga Aiga – Fa'atulagaina e pei o se laulau (Aiga — Fa'atulagaina e pei o le Laulau).
2. Load it into Power Query with the button Mai le Laulau/Fa'asologa tab Faʻamatalaga (Fa'amatalaga — Mai le Laulau/Fa'asologa).
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… I le lisi suiga filifili se au Vaega e (Suiga - Fa'avae I) and set the grouping by customers, and as an aggregating function, select the option laina uma (laina uma). You can name the new column anything you like – for example auiliiliga.
After grouping, we will get a list of unique names of our clients and in the column auiliiliga – 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 Koluma fa'apitoa tab Faʻaopopo le koluma (Add column — Add custom column)ma fa'aofi le faiga lea:
O auiliiliga – 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 (faamaumauga), 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 auiliiliga by right-clicking on its title – Aveese koluma (Remove columns).
After uploading the results to the sheet through Fale — Tapuni ma uta — Tapuni ma uta i totonu (Aiga — Tapuni & Uta — Tapuni & uta i…) 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 Fa'afou ma Fa'asaoina (Fa'afou) po'o le ala pupuu keyboard ctrl+Alt+F5.
- The LOOKUP function is a descendant of VLOOKUP
- How to use the new dynamic array functions SORT, FILTER, and UNIC
- Su'e le sela mulimuli e le o avanoa i se laina po'o se koluma ma le galuega LOOKUP