Formula to confirm cell entries match existing table depending on user chosen on drop down

[diddy47]

hello

I am trying to create a table were a lookup function is used.
If a certain user is picked from timelog column B, the the matching nickname is picked from source column A and the nickname is displayed in timelog column M in the matching row

right now I have the code =VLOOKUP((B3), '[SAFELOG P1W5.xlsm]Source'!$A$3:$B$40, 1, FALSE) but it is returning N/A error?

How can it be set up that if a person picks their name and they enter their tips and service charge in "time log" tab a formula can check if the amounts entered for that user matches those in tab "Tips" for that user

for example Shara, when she picks her name enters her time and tips for the formula to cells (in time log tab) J3 and I3 green (and red if they do not match) if the amounts entered match the values in Tips tab for that user in this case £12.50 and £13.50

I am guessing a look up formula of some type can also achieve this

[Pete_UK]

You can use this formula in M3 of the Time Log sheet:

=IFERROR(INDEX(Source!$A:$A,MATCH(A3,Source!$B:$B,0))&"","")

Copy it down to the bottom of your table.

You would use Conditional Formatting to change the colours automatically, but it is not very clear which sheet you want the colours to appear in - Tips or Time Log ?

Hope this helps.

Pete

[diddy47]

hello , thank you for your reply

The code works perfectly for m3

as for the colour change for j3 and i3, the colour change would be in tab "time log" ...green if the values entered match those in "tips" tab for the selected user ...red if they do not match

[Pepe Le Mokko]

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however the thread title does not really convey what your request is about.

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)

[diddy47]

hello, i hope the amendment reflects the rules now

[Pete_UK]

Put this formula in N3 of the TimeLog sheet:

=IFERROR(VLOOKUP($M3,Tips!$A:$F,COLUMNS($A:A)+4,0),"")

Copy it into O3, then copy both formulae down.

Then select the cells from I3 to J46 and click on Conditional Formatting | New Rule | Use a formula... , then put this formula in the dialogue box which pops up:

=AND(I3<>"",I3=N3)

Click on the format button | Fill tab and choose green, then OK your way out. Repeat, but use this formula:

=AND(I3<>"",I3<>N3)

and choose red for the fill colour.

Excel will automatically adjust the cell references to suit the selected cells.

Hope this helps.

Pete

[diddy47]

thank you for your code and patience, everything works

[Pete_UK]

You're welcome - thanks for the rep.

Pete