Return data of another tab under 3 conditions

[Midoya]

I am trying my best to fix a formula that tracks the values of another tab under 3,conditions ( to match date, Employee No. And In/out signature) .

Actually, I am calculating the attendance of our staff after collecting their finger print data

The formula I did is =If(And($A$2=Date,$B$2=EmpNo,$C$2=In_Out,Vlookup(D4,Data,4,Flase),"")

It always gives me "" as a result

[jason.b75]

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution.

Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

To change a Title go to your first post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

Please note that the revised thread title is still unacceptable in line with the second paragraph of this post.

[Midoya]

I have modified the title above. Thanks alot for the information.

[jason.b75]

I suggest you read my previous reply again, then modify your title again.

[FDibbins]

I have edited your title for you.

Looking at your formula...
=If(And($A$2=Date,$B$2=EmpNo,$C$2=In_Out,Vlookup(D4,Data,4,Flase),"")

are Date,EmpNo and In_Out range names?

If so, are you sure that they all are identical to the cells they are testing?

[Midoya]

Yes, they are.

The refers to the correct cells . I have enclosed herewith the sheet for your review. Thank you.

Reagrds,
Yahia

[jason.b75]

I have edited your title for you.

Looks the same to me, Ford.

[Midoya]

Hi! Any update?

[JohnTopley]

In E6

IFERROR(INDEX(Time,MATCH($A$2&$B6&E$5,EmpNo&Date&InOut,0),0),"")

Enter with Ctrl+Shift+Enter

Copy (drag) across to F5

Copy down both columns

OR (more efficient)


=IFERROR(INDEX(Time,MATCH(1,(EmpNo=$A$2)*(Date=$B6)*(InOut=E$5),0),0),"")

Entered as above

I would advise you change your named ranges to defined maximum rather than whole columns e.g. A2:A1000 to avoid performance problems.


FYI: in A6 put "=B6" (no quotes) and format as "DDD"

[Midoya]

Thank you , John,

Unfortunately, still does not work

Regards,
Yahia

[JohnTopley]

It would if you had the correct name ..

=IFERROR(INDEX(Time,MATCH(1,(EmpNo=$A$2)*(Dates=$B6)*(InOut=E$5),0),0),"")

You must have changed the name from "Date" to "Dates"

[Midoya]

Yeah, you are right. I have changed the name. however, I corrected it now , still does not work

[kvsrinivasamurthy]

Perhaps not entered as array formula.
To enter ARRAY formula
Paste the formula
Press F2
Press Ctrl+Shift+Enter keys together.
formula will be covered with{} brackets

[FDibbins]

Looks the same to me, Ford.

You are correct, I could have sworn I changed it - well, It's changed now

[Midoya]

Sorry guys for being late! Thank you all for your help it is working right now with me and I have finished up the payroll because of your help. Thank you once again. Appreciated.

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.