in this video i'm going to show you how to pull data from another sheet based on a criteria in
google sheets and excel it's pretty much going to be the same formula we may need a little bit of
extra thing in there to make it really useful in excel but overall it's going to be the same thing
so let's say i have this worksheet and i want to grab only female from this column
and basically filter the data on another sheet so i'll basically just add another worksheet
i will call it data pool
and here what i'm gonna do i'm gonna start with a filter function equals filter i'm skipping the
first row here because i'm gonna do headers there on top so i'll do equals filter and then we'll go
to our customers worksheet we'll basically just highlight this data not including the headers
even though if you include headers it's not going to be the end of the world
comma and then after the comma we're going to do the criteria so i'm going to select this column
of genders again you want to make sure whatever range size you have here
matches the range size over here so from 2 to 12 from 2 to 12. so i want to make sure that that
equals to now because these are text we're going to do female in quotes if it was a
number you wouldn't use codes just type your number so close parentheses hit enter
and as you can see we got our data just like that i'm going to just copy paste headers
so just like that so the same formula should pretty much work in excel so if i just copy this
go to excel and just create another sheet and literally just paste it here
so as you can see i got the same results and then i can just copy paste headers
and call it data pull
so there it is that's our data so now let's say we want to have some sort of drop down so i'll go
to google sheets first and do this here and then i'll do it in excel 2 which is again going to be
pretty much the same thing so let's say we want something like gender and we want something that
says male or female and basically based on this value we want this data to be filtered
so what i can do i can go back to this first formula here on top remove this quotes female
including the codes and after the equal sign we'll just click on mail hit enter and see that's male
if i change this to female that should give us female and we can convert this to
a drop down data data validation we can do list of items and here we can do female comma mail
reject any other input hit save so now we should have
this see drop down where we can choose one or the other and we get our list and again
the same thing you could do in excel so let's just add some space here on top type gender
we'll type the gender here we'll convert this to a drop down
we'll go under data and do data validation data validation and here we're going to do list
and as source we'll just do female comma male no spaces when you do these things
just a comma you want to make sure you type this correctly not the way i just did
so now i should have this drop down so finally let's connect that to
this formula in here in this first cell so we'll just go in here remove this female in quotes
and just link to the cell that says female hit enter and if i just filter this to mail
you'll see it filters to mail just like that now the problem here with excel that you're
going to have with this function that you do here if you pay attention right here see for
this middle initial for jose i got 0. now let's go back to our data and take a look see we got middle
initial it's blank now in google sheets we're not going to have this problem so if i go here and do
mail see jose it's just blank no problem in excel it has a weird thing with blanks and zeros
so as you can see it pulls the zero which kind of sucks so what we're gonna do we're gonna go
back to this function i'm going to change it a little bit so what i'm going to do this first
range that i did that goes from a2 to k12 i'm just going to add an if function around this so i'm
going to say if open parenthesis that range let's just do some spacing that range equals to blank
comma then return blank comma otherwise we're going to just basically just return the same
range so i'm just going to copy that range reference paste it here and close parentheses
so i'm gonna do this if wrapper around our main function hit enter and what you're gonna see now
the blank is actually returns blank so this way we don't have that weird thing happening with blanks
you could do a bunch of different conditions here so if you wanted to do more than one condition
let's say in case of google sheets you could just go here add a comma and then after the comma you
do another condition so for example let's say you wanted to filter by the zip codes and i'm
going to think about this as a number just to show you an example with a number sort of thing
but you know the id is you could use any column to filter by in this case let's say as a second
column i want to make sure the zip codes are above a certain number so i'll just go in here and add a
comma and after the comma we can go and highlight another range from our source data our zip codes
again it needs to match the size as this one and i'm going to say it should be let's say greater
than or greater than or equal to you can do things like that instead of just equal to so let's say we
want anything that's above nine thousand so if i'd enter well i guess nine thousand wasn't a
good idea because this is like thirty thousand it should be ninety thousand so let's actually change
that to ninety thousand this is why we should probably work with actual number data not the
zip codes but there it is we can see now we have a number now the reason i did this i wanted to
show you see for a number type we don't really do quotes around this number i just typed 90 000. for
text i used to do those quotations but if you do a cell reference let's say just type like 90 000
here and refer to that cell then it's really not going to matter that much so we can just
remove this and link to that cell and there it is and you could change this to
let's say 60 000 and now we got those so it works so we have other types of comparisons here other
than just equals two and you can basically just keep doing commas and doing more conditions
and doing other columns just remember that every condition you do is an end condition so you're
saying this and this must be true at the same time and you could do the same thing in excel
the same way and that should do it thanks for watching and i'll see in the next video