Comparing value to master coloumn Solved/Closed

Question

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

rizvisa1 · Answer

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.

rizvisa1 · Answer

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

albatros · Answer

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

rizvisa1 · Answer

email me as rizvisa@hotmail.com

rizvisa1 · Answer

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

rizvisa1 · Answer

What you are getting ?
I am getting 11. If I look at master for tuesday for that city it is 11.

rizvisa1 · Answer

Ok I just emailed your workbook too.

Comparing value to master coloumn

7 responses

Similar discussions

Subscribe To Our Newsletter!