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.

I need to vlookup with 2 different criteria, so I will be doing: IF this name occurs on this date, then how many hours did they work?
Solved by T. S. in 60 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
Excelchat Expert 01/08/2018 - 06:09
Hi…Welcome to Got It Pro
User 01/08/2018 - 06:10
Hi
Excelchat Expert 01/08/2018 - 06:10
I see you are illustrating some data in the preview sheet.
User 01/08/2018 - 06:11
You watching?
User 01/08/2018 - 06:11
Oh woops
Excelchat Expert 01/08/2018 - 06:11
Yep
User 01/08/2018 - 06:11
yes I am, i did not see your message
Excelchat Expert 01/08/2018 - 06:11
No problem :)
Excelchat Expert 01/08/2018 - 06:11
Please complete and let me know what logic you require.
User 01/08/2018 - 06:12
complete, I need to vlookup with 2 criteria, so it'll be if A5 has a b3, than look up hours
User 01/08/2018 - 06:13
first sheet I am using as a better display and sheet2 will be the source data
Excelchat Expert 01/08/2018 - 06:13
ok so look for EE ID and Date in Sheet 2 and fetch the hours, right?
User 01/08/2018 - 06:13
Yes
Excelchat Expert 01/08/2018 - 06:13
Perfect...let me create the formula.
User 01/08/2018 - 06:14
I expect there to be a lot of errors so if we could do IFERROR( ,0)
Excelchat Expert 01/08/2018 - 06:14
Sure
Excelchat Expert 01/08/2018 - 06:18
Ok...the formula is ready.
User 01/08/2018 - 06:19
Could you please break it down for me on how it works?
Excelchat Expert 01/08/2018 - 06:19
Sure..
Excelchat Expert 01/08/2018 - 06:20
Give me a quick moment...my screen just froze
Excelchat Expert 01/08/2018 - 06:21
Ok..so this is array formula.
Excelchat Expert 01/08/2018 - 06:22
And if you are working this out in excel, you will need to hit a key combo of Control+Shift+Enter instead of just Enter.
Excelchat Expert 01/08/2018 - 06:22
Now let me explain the formula.
Excelchat Expert 01/08/2018 - 06:22
This is an Index Match lookup function.
Excelchat Expert 01/08/2018 - 06:24
Syntax = Iferror( Index(Lookup table, Match(Lookup value 1 & Lookup value 2, Lookup array 1 & Lookup array 2, 0), result column index),0)
Excelchat Expert 01/08/2018 - 06:24
So our lookup table is Sheet2!$A:$G
User 01/08/2018 - 06:25
=IFERROR(INDEX($B4&I$3,MATCH('ADP 10-122017'!$A:$A&'ADP 10-122017'!$E:$E,0),15),0)
User 01/08/2018 - 06:26
That is what I am running, my column B is your column A, my column I is your column b
Excelchat Expert 01/08/2018 - 06:26
Here I will like to give you a little more info regarding array formulas. This certainly is a helpful function in situation like yours where we are looking up 2 criteria in 2 rows, but takes enormous times to process. So to improve efficiency, please use only range where your data exist instead of entire column.
Excelchat Expert 01/08/2018 - 06:27
So in excel I will change the lookup table range to Sheet2!$A$3:$G$2797
User 01/08/2018 - 06:27
mind if I restart the data and set everything exactly how I have mine?
Excelchat Expert 01/08/2018 - 06:27
Sure...please do
Excelchat Expert 01/08/2018 - 06:28
But the formula is syntax is wrong in the formula you are using
User 01/08/2018 - 06:30
Okay, it is not exactly like mine
User 01/08/2018 - 06:31
okay
Excelchat Expert 01/08/2018 - 06:33
I see its almost the same data structure like earlier
User 01/08/2018 - 06:34
Yes, just wanted to move rows and everything over so it would be the exact same
Excelchat Expert 01/08/2018 - 06:34
So your EEID is in column B
Excelchat Expert 01/08/2018 - 06:35
And your dates starts from column I, is that so?
User 01/08/2018 - 06:35
EE is currently in Column A, and dates starts in Column B
User 01/08/2018 - 06:36
on sheet2 EE in column A, dates in Column E, hours in column G
Excelchat Expert 01/08/2018 - 06:36
Yep..that's how it is currently
Excelchat Expert 01/08/2018 - 06:36
What changes you are trying to do?
User 01/08/2018 - 06:36
Okay, could we do the same formula again?
Excelchat Expert 01/08/2018 - 06:37
ok..let's do it but only for 5 rows as its taking too long to process being array.
Excelchat Expert 01/08/2018 - 06:40
You will notice I have chosen the range till row 43490 in Sheet2 as our lookup table extends till row 43490. This is to improve efficiency of array formulas.
Excelchat Expert 01/08/2018 - 06:41
INDEX(Sheet2!$A$1:$G$43490 is indexing the lookup table
Excelchat Expert 01/08/2018 - 06:41
MATCH($A4&B$3,Sheet2!$A$1:$A$43490&Sheet2!$E$1:$E$43490,0)
Excelchat Expert 01/08/2018 - 06:42
This is looking for match of EE ID in column A in sheet2 and date from 3rd row in Sheet 1 in column E in Sheet2
Excelchat Expert 01/08/2018 - 06:42
0 at the end of this match signifies to find the exact match of both this criteria
User 01/08/2018 - 06:42
it is telling me there is a problem with the forumla starting at the first $A$1
Excelchat Expert 01/08/2018 - 06:43
,7) is the column index which is column G in Sheet 2 from where we want hours
Excelchat Expert 01/08/2018 - 06:43
You mean in your excel?
Excelchat Expert 01/08/2018 - 06:44
What problem?
User 01/08/2018 - 06:44
Yes, got past it.
Excelchat Expert 01/08/2018 - 06:44
Are you aware of array formulas?
User 01/08/2018 - 06:44
I will send you my forumla now. the ONLY difference between our sheets now is that my hours are in column O and yours are in column G
Excelchat Expert 01/08/2018 - 06:45
If so, I am sure you know that the formula gets enclosed in curly brackets once you hit Control+Shift+Enter
User 01/08/2018 - 06:45
=IFERROR(INDEX(Sheet2!$A$1:$O$43490,MATCH($A4&B$3,Sheet2!$A$1:$A$43490&Sheet2!$E$1:$E$43490,0),15),0)
User 01/08/2018 - 06:46
Heyyy its working now!
Excelchat Expert 01/08/2018 - 06:47
Perfect :)
Excelchat Expert 01/08/2018 - 06:47
Is there anything else I can help you with this query?
User 01/08/2018 - 06:48
How do you drag it down with all of these abosultes on it? it is giving me either "can not change an array" or it is giving me all the same returns
Excelchat Expert 01/08/2018 - 06:49
That is because there are other array formulas that you have inserted earlier on your sheet.
Excelchat Expert 01/08/2018 - 06:49
Clear all the array formulas first.
Excelchat Expert 01/08/2018 - 06:49
Then insert the formula that you just gave in cell B4.
Excelchat Expert 01/08/2018 - 06:50
And make it array formula with the key combo.
Excelchat Expert 01/08/2018 - 06:50
The absolute and relative reference in Match lookup that I have already placed is taking care when you drag the formula.
Excelchat Expert 01/08/2018 - 06:51
So you can just either copy paste or drag this formulas sideways as well as down.
Excelchat Expert 01/08/2018 - 06:51
It will give same returns if you haven't made it an array formula.
User 01/08/2018 - 06:51
The CTRL Shift Enter does not always put the brackets on
Excelchat Expert 01/08/2018 - 06:52
Yes, it will.
User 01/08/2018 - 06:52
does manually adding the brackets do it as well, or might i see some problems with it
Excelchat Expert 01/08/2018 - 06:53
Just make sure after putting the formula in the cell, in typing mode you hit the key combo. Alternatively, select the cell where the formula is and press F2, then hit the key combo.
Excelchat Expert 01/08/2018 - 06:53
NO, you cannot add it manually.
User 01/08/2018 - 06:53
F2, then entered it worked, thank you
Excelchat Expert 01/08/2018 - 06:54
Does it enclosed in curly brackets?
User 01/08/2018 - 06:55
Wow this takes a long time to load the whole thing ha
Excelchat Expert 01/08/2018 - 06:55
If you are only hitting Enter, it will not get the curly brackets and its not a array formula and you will not get the result
Excelchat Expert 01/08/2018 - 06:56
Yes, as I have already mentioned earlier, array formula takes much longer to process.
User 01/08/2018 - 06:56
Hmm, what is your guys pricing?
User 01/08/2018 - 06:57
Or girls*
Excelchat Expert 01/08/2018 - 06:57
You can either go for per session pricing or take a subscription.
Excelchat Expert 01/08/2018 - 06:58
And if you are asking how much we get paid, we do get paid by session.
User 01/08/2018 - 06:58
I was not but I am always interested in these things :)
User 01/08/2018 - 06:58
I am very impressed with how fast you were able to do this, may I ask your credentials?
User 01/08/2018 - 06:59
Or if you have another job. You could make a killing working for an accounting consulting firm
Excelchat Expert 01/08/2018 - 06:59
Its kind of a hobby to do anything excel...and this formulas and functions comes handy from a long years of experience.
User 01/08/2018 - 07:00
That's awesome
Excelchat Expert 01/08/2018 - 07:01
Yep...love excel and numbers :) and work on variety of excel project ranging from such simple formulas to highly advanced functionalities.
User 01/08/2018 - 07:01
Hey so I tried going down to row 400 out of 43k and it took quite a while. Any advice on how to get it to run smoothly with that many rows and columns?
Excelchat Expert 01/08/2018 - 07:02
Well..with array its going to take that much time.
Excelchat Expert 01/08/2018 - 07:02
There are 2 things I can suggest.
Excelchat Expert 01/08/2018 - 07:04
Turn your calculation mode to Manual while you place the formula and drag it all the way down. Then turn on calculation mode to automatic, then leave excel for sometime to complete the calculation. During this time, you won't be able to work on any other excel.
Excelchat Expert 01/08/2018 - 07:05
2. Create a helper column in Sheet 2 and concatenate EE ID and Dates together in one column. Then instead of doing array match in 2 columns, you can do match in just the concatenated column.
Excelchat Expert 01/08/2018 - 07:05
Then this will be normal formula and not array while will not take time to process.
User 01/08/2018 - 07:06
oh my god why did I not think of that!!! ahhh. I could have concatenated this all from the beggining probably then huh?
Excelchat Expert 01/08/2018 - 07:06
Yep..there are immense possibilities in excel
User 01/08/2018 - 07:07
Did you take any courses in a college or just online to learn?
Excelchat Expert 01/08/2018 - 07:07
Just learnt on my own out of interest in excel :)
Excelchat Expert 01/08/2018 - 07:08
I hope you can get it going easily with the Concatenate logic.
Excelchat Expert 01/08/2018 - 07:08
If we had more time, I could have done a demo. But we are just left with 1 min for session to end.
Excelchat Expert 01/08/2018 - 07:08
Please do give your valuable feedback of our service at the exit of this session.
Excelchat Expert 01/08/2018 - 07:08
Thanks for your time. Please do come back for any new question. You can now end this session. Have a great day ahead!
User 01/08/2018 - 07:09
you cant have more than 65472 formulas that refer to another sheet :(
User 01/08/2018 - 07:09
array forumulas*
User 01/08/2018 - 07:09
Maybe paste value them?
Excelchat Expert 01/08/2018 - 07:09
Depends on the version of excel
User 01/08/2018 - 07:09
and continue it

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
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc