**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.*