Comparing value to master coloumn

Solved/Closed
-
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
-
Hello,
I am basic excel user. I have issue where i need to compare value from master table and show in the sheet. Below is the actual example. The Sheet shows Day and Acutal values next to which is expected coloumn. I need to pull value from Expected Master sheet , basically if it is TMC and Tuesday then under expected it should put the value of 10. What formula should i use and how???

Please help me

Day TMC Expected WBC
Tuesday 12 3
Wednesday 9 2
Thursday 8 4
Master Expected
TMC WBC
Tuesday 10 4
Wednesday 5 3
Thursday 4 2

7 replies

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
Could you please put some some sort a separator between the columns, as in the first i see 3 col headers and 3 col values, and in the bottom i see two column.
Thank you for reply me, to explain you correctly

1) There is first coloumn for day( which has value from monday to saturday) , Second coloumn is total appointment made for location A, Third coloumn is expected appointments.

2) I need a formula which fills value on the third coloumn from Master Appointment list

3) Master Appointment has two coloumns locations ( vertical) and Day( horizontal)

So basically formula should be if the location is X and Day is Y then value in expected coloumn should from Master Appointment list.

Please suggest i would be really appreciate it. I can send the excel sheet example if needed.
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
Ok this is how I understood your workbook,which sorry still confuses me. See what I did below helps you out (after~~~~~~~~~~)


Day                   TMC        WBC
Tuesday              12           3
Wednesday           9           2
Thursday              8           4




                      TMC       WBC
Tuesday            10          4
Wednesday         5          3
Thursday            4           2



~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Sheet 1
        A                   B        C          D                   E
1      Day              TMC    WBC   Expected_TMC   Expected_WBC
2      Tuesday        12	       3
3      Wednesday    9	       2
4      Thursday       8         4



Master Sheet
	
           A                         B                             C
1      	                      Expected_TMC	      Expected_WBC
2      Tuesday                 10                             4
3      Wednesday              5                             3
4      Thursday                 4                             2



you can use this formula at the intersection of Expected_tmc and Tuesday
=INDEX(Master!$B$2:$C$4, MATCH($A2, Master!$A$2:$A$4 ,0), MATCH(D$1, Master!$B$1:$C$1, 0))

you can use this formula at the intersection of Expected_WBC and Tuesday
=INDEX(Master!$B$2:$C$4, MATCH($A2, Master!$A$2:$A$4, 0),MATCH(E$1, Master!$B$1:$C$1, 0))


Simple drag down to fill in the rest of the columns
Hello,

I tried your formula and it gives me error. I guess i am not able to explain you correctly the scenario.

How can I attach the excel sheet here? Or would be Ok if i can send you the excel sheet , it is very small in size 37KB only in size. Please let me know.

Waiting for response. Thanks again
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
email me as rizvisa@hotmail.com
I have just emailed you the file.

Thanks
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
Do two things

Here is the formula for cell d2
=IF(ISERROR(VLOOKUP(TEXT(B2,"dddd"),MASTER!$A$1:$J$6,2,FALSE)), "",VLOOKUP(TEXT(B2,"dddd"),MASTER!$A$1:$J$6,2,FALSE))


Also change d1 on master to this Annapolis-Expected

you have do same for rest too


Here is what u need to change for each different city

Change #1 for each city : where is says "2, false" in the formula. right now "2" refers to column #2 where "Annapolis-Expected" on master. you have to change this number to the column where other cities are eg. 5 for Rockville-Expected


Sorry to cause trouble, but i have deadline approaching. I have emailed you the sheet for your reference. I pasted the formula you gave, on cell d2 itself it is not working...It gives wrong values.

Please suggest
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
What you are getting ?
I am getting 11. If I look at master for tuesday for that city it is 11.
HURRAY!!!!!!!!!!!!! You are awesome. I made some mistake i think while copying. It works correctly.

THANK YOU...YOU ARE A CHAMPION
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
Ok I just emailed your workbook too.