From the course: Excel: Advanced Formulas and Functions

Challenge 3: Guitars

From the course: Excel: Advanced Formulas and Functions

Start my 1-month free trial

Challenge 3: Guitars

“

(upbeat energetic music) - Amy is a guitar player and a guitar collector. Here is a list of guitars that she's bought and sold over the years. And is sorted by the amount descending. Here's what we want to know. Does Amy still own guitars: F, L, M, or A? If she's never owned one, then return N, period. O, period. Well, let's look at the data. So on the 25th of August of 2013, Amy sold guitar M. We also see on the 6th of June of 2020, she purchased guitar M. We see that for guitar J, that was purchased on the 17th of October 21 and sold the 5th of December of 21. So, we want to know about guitars F, L, M and A specifically. Does she still own them or not? Right? Pause the video and work that out. Come back. (upbeat energetic guitar music) All right, let's do it. Let's see if your result matches my result. I'm going to go guitar and we're interested in F, L, M and A. Make this bold. Slide this out of the way and I'm going to put this into a table. Okay. Get rid of this filter buttons. Okay. And in order to do this right I've got to sort the activity, go to data and sort it ascending. Now, equals x lookup, double click Look up what? F, comma, look up array. This column, comma, return array, the action column, comma if not found, double quote N, period, O, period, double quote, comma, match mode, We want an exact match. Nothing to do there, comma. Search last to first negative one closed parenthesis, and enter. So the last activity on guitar F was a purchase so we can assume that she still owns that one. And let's see, she never owned a guitar L and even though she had sold M at one point she bought it back and A was purchased. So she owns F M and A and never did on L. Well, let's verify M before we get out of here. There's three of them. Purchased for $710 in May of 99 and then sold for $1,230 August, 2013 and bought back for $800 in June of 2020.

Contents