Excel - How to Use VLOOKUP and IF Function Together - Expert Solution

Question description:
This user has given permission to use the problem statement for this blog.

Hi , I have all my active jobs on one sheet and I need to be able to have another sheet that links to the first with all the active jobs, then another sheet but with the inactive jobs. I'm not sure if its best to do this through filtering or Vlookup, i have no clue
Solved by K. F. in 40 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
Excelchat Expert 13/04/2018 - 01:53
User 13/04/2018 - 01:53
little experience, i use it for accounting and budgeting
Excelchat Expert 13/04/2018 - 01:53
can you please share the files you are working on
User 13/04/2018 - 01:54
unfortunately no because it's sensitive information
Excelchat Expert 13/04/2018 - 01:54
can you copy paste the part you are facing problem?
Excelchat Expert 13/04/2018 - 01:54
in the shared view?
User 13/04/2018 - 01:55
so i have many active jobs and inactive job like what i pasted here
Excelchat Expert 13/04/2018 - 01:55
User 13/04/2018 - 01:55
need to be able to sort the active jobs from the inactive job on a separate sheet with all its info
Excelchat Expert 13/04/2018 - 01:55
please paste the part in the shared link
User 13/04/2018 - 01:56
like that?
Excelchat Expert 13/04/2018 - 01:56
Excelchat Expert 13/04/2018 - 01:57
so how active and inactive jobs are defined?
User 13/04/2018 - 01:57
well if we stop working on them I have to somehow label them as inactive
User 13/04/2018 - 01:58
i dont know the best way to do that in excel to be able to link it to another spread sheet
Excelchat Expert 13/04/2018 - 01:59
in this case how would you like to present the active/inactive...?
User 13/04/2018 - 01:59
inactive to be shaded out and the active not to be?
User 13/04/2018 - 01:59
is that what you mean?
User 13/04/2018 - 02:00
is the best way to label it through naming the cell?
Excelchat Expert 13/04/2018 - 02:00
so by inactive do you mean the ones with blank?
User 13/04/2018 - 02:00
no so the number 1447 for example is inactive
User 13/04/2018 - 02:00
its a darker colour
User 13/04/2018 - 02:01
thats the only way we have been distinguishing the difference at the moment
Excelchat Expert 13/04/2018 - 02:03
there is no way to distinguish active and inactive without color?
User 13/04/2018 - 02:03
User 13/04/2018 - 02:03
unless we assign a colum next to the job that says active or inactive
Excelchat Expert 13/04/2018 - 02:04
Is that possible to do so?
User 13/04/2018 - 02:04
why not , sure
User 13/04/2018 - 02:04
then what would the formula be to get the information into a seperate sheet?
Excelchat Expert 13/04/2018 - 02:04
if thats possible then after that you need to separate the active and inactive datas in two different sheets
Excelchat Expert 13/04/2018 - 02:05
User 13/04/2018 - 02:05
User 13/04/2018 - 02:10
is that possible
Excelchat Expert 13/04/2018 - 02:10
should be
User 13/04/2018 - 02:11
so how do we do it
User 13/04/2018 - 02:11
Excelchat Expert 13/04/2018 - 02:11
wait a bit
User 13/04/2018 - 02:11
oh i see you doing it now
User 13/04/2018 - 02:11
Excelchat Expert 13/04/2018 - 02:16
[Uploaded an Excel file]
Excelchat Expert 13/04/2018 - 02:17
Excelchat Expert 13/04/2018 - 02:17
where there is no more data num error appears
Excelchat Expert 13/04/2018 - 02:18
have you checked the file?
User 13/04/2018 - 02:18
great let me check it works on my file
User 13/04/2018 - 02:20
i copy and paste the formula in and it just gives me 0
Excelchat Expert 13/04/2018 - 02:20
Insert the formula with ctrl+shift+enter
Excelchat Expert 13/04/2018 - 02:20
instead of just enter
User 13/04/2018 - 02:21
im trying to move the formula into my spreadsheet though
User 13/04/2018 - 02:22
and it take me back to yours
Excelchat Expert 13/04/2018 - 02:23
yes, there may be some cell reference mismatch.....because the sheet name cell address may be different
Excelchat Expert 13/04/2018 - 02:23
I cold have helped if you share your file
User 13/04/2018 - 02:23
i should just be able to replace that though/
User 13/04/2018 - 02:23
its ok i think i can work it out with your spreadsheet
User 13/04/2018 - 02:23
thankyou so much
Excelchat Expert 13/04/2018 - 02:24
most welcome, so if you leave please end the chat from your ed and give me ratings, thanks

This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user information.

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.

Click here to get your free Excelchat help session

Subscribe to Excelchat.co
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
The Allstate Corporation
United Parcel Service
Dell Inc