Excel Magic Trick 358: Part 1: Return Multiple Items From One Lookup Value for Table w Formula

welcome to excel metric trick number 358 hey if you want to download this workbook and follow along click on my youtube channel then click on my college website link and you can download the workbook excel metric 358 to 369 trick 358 359 and 360 are all related and we're going to talk about an amazing topic the topic is how to return multiple items from one lookup value and we're going to do it where we create a table of values a column and a row in our first example we're going to be looking at football betting data and we're going to return a whole table of data in our second example we'll be looking up a column with numbers and words and we're going to return all of the numbers in a column and finally we'll be looking up like a little database with devices with duplicates in the first column we want to return these but we're going to return them in a row now these are all related because we're we're talking about returning multiple items from one lookup value now that's one way to think of it another way to think of this is if you have duplicates if we want to return all of this data for say week 2 or week 11 there's 1 2 3 look at that so this really could be thought of not not just as return looking up one value 11 and returning multiple records it could also be thought of as what do you do when you have duplicates in the lookup column now I have done other videos on these topics but what I'm going to try to do here let show you boom boom boom three examples all in a row and use different terminology to describe it and title the videos different because this is one of the most common questions I get and even though I've done a bunch of videos no one can seem to find them so I'll try to do a better job of explain them alright here's the setup betting table we not only want to in this first video see how to look up a week but we also want to find look up a second criteria w click over here we want to return all the records that match 11-week and win you can think of this as one value we're going to look up one value and what is it 11 w so we're looking up one value returning multiple items and there are duplicates in fact I think they're for week 11 there's three of them there's three records that match 11 W now the first thing is we need to count so for all three of these videos we're going to have to count how many there are because we're going to have to return a number of items and since we're doing it with a formula we need to turn off the formula when it gets past however many records there are so we're going to use we could use some product and there's the formula right there beautiful formula or work if you have 2007 or 2010 coming up then you want to use countifs it's faster now criteria range and criteria we're going to do one two so I'm going to click on the sheet' and the first criteria is week click on the top control shift down arrow now I'm going to actually try and build my formula up here equals whoops let me scroll down here Shh count there's the criteria range I'm going to type not and equals a comma and the criteria now if I click back on this sheet right here because we do need that cell reference it'll start building a sheet reference that says week one and we don't want that since the formula resides on that sheet so watch this I'm just going to cheat here and type eleven and I'm going to change it when I enter the formula in later now the criteria range two is going to be my win-loss column control shift down arrow I'm going to type a comma criteria two is this is text so I have to put it in double quotes close parenthesis there's our formula ctrl enter I'm immediately going to hit f2 to edit double click that 11 double click that w and boom so there's two records if I change this to se6 there's one if i change it to two how many are there there's two for loss there's two criteria here now let's go and build our formula alright we're going to start off with equals if and I'm going to use the Rose this is a way of incrementing a number remember because our formula needs to always look there as soon as it exceeds two it's got to show blank if it says five it needs to show five records and the rest blanks that's because we're in essence extracting data with a formula now Rose were in a seven so we type a dollar sign seven colon and then that was an absolute now we'll do a relative now notice what happens with this incrementer that's called an increment locked not locked right now Rose says how many rows are there seven two seven is one seven two eight when it gets down here will be two etc so it increments a number inside the formula that's got to be less than or equal to two when it's less than or equal to two and we better lock that to all directions what do we want we want to do our lookup and we're going to use index and match so index now index we're going to say we're going to give it an array and a row number now the only thing we need for this formula here is row number because we have these columns over at the other table watch this I'm going to say the array notice bet opposing week result I'm going to do the first array click on this sheet oh do we set it up the same year boom-boom-boom so we can do one column at a time when we copy it over to the next column it will work control shift down arrow and I immediately want you to hit the f4 key once and twice the reason why is that has to be locked notice the dancing ants are dancing around that range but when this formula in this sheet over here when we copy it over the dancing ants will move because only the row reference is locked and not the column alright here's our screen tip now I'm going to type comma and I'm actually going to pull this down here comma what do we need to do for row number now remember the problem the whole problem with this task at hand is we're returning multiple records well in index needs a row number well here's a to know let's look at the 11 since that's the 11 there's an 11 so we really need 1 2 3 4 5 6 7 8 we need an 8 for the formula but when it copies down in the next row on this other sheet it needs to go to 8 9 10 11 12 13 14 15 16 how in the world do we do that how do we get multiple row numbers for our index function when we're copying the formula down that's where the small function comes in remember our goal is to get for the index right in this argument right here a row number since we have multiple ones we're going to use small small will give us the smallest if we tell it one it'll give us the smallest row number if we give it a two it'll give us the next smallest row non remember we had like I think nine and eleven or nine and 16 so small wants to give us nine but as the formula copies down then we need a 16 so how do we do that well first off realize we have two criteria week a week and a win-loss so the array inside of here we don't want all the records we just want the the matching ones 9 16 etc so watch this we have two criteria so we have to use two ifs if the first one is I click there ctrl shift down arrow and you got to hit f4 because that's going to be locked in all directions if that's equal to what I'm going to do my same trick I'm going to type 11 I will edit that later because the beauty of all this wouldn't work if we left that hard-coded the next criteria notice it's waiting for a value of true but if you have a second criteria that's where you put your if we click in this column right here ctrl shift down arrow and f4 and that one has to be equal to what a w and so we type it in its text so we put it in double quotes all right so we have our two ifs now what's our comma and our value is true remember the small has to deliver some row numbers to the index right so we really want ro now we want you can pick whichever column of row numbers you want I'm just going to pick this one control shift down R and this is going to be F forward in all direction but notice right now row function is program it's going 16 to 39 right now would give us 16 17 18 that's not what we want the index needs 1 for this record - for this record so I close parentheses - row again and watch this tricky little thing I click there and hit f4 look at that now it's got row 16 - 16 and by the way this is array so this will give us all of the row numbers - this one 16 - 16 is 0 16 - 17 is 1 but that's not what we want we want instead of 0 1 2 3 4 5 we want 1 2 3 1 2 3 4 etc so we just +1 boom that is the value if true now all of this if if we close parentheses notice the if is still up because there's two if so I close parentheses again boom that if and those rows are going to deliver a whole array of row numbers to the small so in essence it's going to give us 1 2 3 4 is that awesome and it's only going to give us the row numbers that are relevant because we put if with the criteria and if with the criteria all right now how do we get the small to give us the first smallest second small so we'll use the same incrementer here 1 2 3 4 5 isn't that cool so we click at the end here it's waiting for K so I type a comma the K is going to be control control V that is our rows now it's waiting close parentheses I love these screen tips they help out so much in big formats now that small I'll deliver the row so I'm going to close parentheses and the value of true that is the index comma and then finally the value of false remember we want the formula to turn off so we double click for blank close parenthesis now this is an array formula so what do we do hold ctrl shift and enter now I'm going to immediately hit f2 because we've violated the beauty of Excel double click that click on the criteria and you gotta lock it in all directions and then double-click this w click on that one locking in all directions control shift down arrow I'm going to copy this over I'm going to copy it down all the way and you could make this however big your table is I'm going to pull this up all right now let's test it 11 and we got a win so there's two of them we go to 10 oh we had no wins hi do we have any losses and we said no we didn't make any bets in week two six there's one loss and how many wins one let's try one more too actually I'm going to put this as one you don't need to use the drop-down you can just type so we have three wins and week ones now watch this we have week one in quotes there I mean in parentheses we set up our sheet name properly so when I copy it over it will increment week 1 week 2 week 3 then we can have a sheet for each week but watch this how do you copy the sheet not right click copy you hold ctrl click on the sheet and when you drag it up you see that plus that means copy the little black arrow means I'm going to drop it right there and the key to this is to let go of the mouse not ctrl and then you could continue that on we can make this too and boom we'd have all of our week's bets all 11 or 12 or 13 of them all set up and all dynamically linked so if we had to enter data in here win win win loss these would all update so this is video number 1 for returning multiple items given one lookup when we come back we'll do a column and then finally we'll do a row see you next video

Channel: ExcelIsFun

Views: 332,809

Rating: 4.8666668 out of 5

Keywords: Excel, Formula, two, criteria, for, the, data, extraction, INDEX, MATCH, functions, Data, Extract, excelisfun, Mike, Gel, Girvin, Highline, Community, College, Lookup, One, Item, Return, Many, Items, #1

Id: Tp7I5u1MqiM

Length: 12min 43sec (763 seconds)

Published: Thu Aug 13 2009