Here is a simple yet novel use of formulas. Let us say you have a list of values in range A1:A5 and you want to reverse the list.
In an empty cell write =INDEX($A$1:$A$5,6-ROWS($A$1:A1)) and copy down.
Boom, you get the reversed list.
Here is how the formula works:
- In the reversed list, first item is last item in the original list (ie 5th item in our case).
- INDEX() formula takes a list, a row number (and optional column number) and returns the value at intersection.
- In this case, $A$1:$A$5 is the list.
- 5 is the size of list.
- ROWS($A$1:A1) gives running numbers from 1 thru 5 when copied in any range of 5 cells. Read more on using ROWS() formula.
More on lists: Shuffle a list of numbers | Sort a list of texts using formulas | Remove duplicates from a list
One Response to “How to compare two Excel sheets using VLOOKUP? [FREE Template]”
Maybe I missed it, but this method doesn't include data from James that isn't contained in Sara's data.
I added a new sheet, and named the ranges for Sara and James.
Maybe something like:
B2: =SORT(UNIQUE(VSTACK(SaraCust, JamesCust)))
C2: =XLOOKUP(B2#,SaraCust,SaraPaid,"Missing")
D2: =XLOOKUP(B2#,JamesCust, JamesPaid,"Missing")
E2: =IF(ISERROR(C2#+D2#),"Missing",IF(C2#=D2#,"Yes","No"))
Then we can still do similar conditional formatting. But this will pull in data missing from Sara's sheet as well.