Comparing value to master coloumn

Solved/Closed
albatros - Jan 31, 2010 at 01:28 PM
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Feb 1, 2010 at 09:47 AM
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

rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 768
Jan 31, 2010 at 02:17 PM
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.
0
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.
0
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 768
Jan 31, 2010 at 06:19 PM
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
0
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
0
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 768
Jan 31, 2010 at 08:59 PM
email me as rizvisa@hotmail.com
0
I have just emailed you the file.

Thanks
0

Didn't find the answer you are looking for?

Ask a question
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 768
Jan 31, 2010 at 09:44 PM
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


0
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
0
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 768
Feb 1, 2010 at 09:35 AM
What you are getting ?
I am getting 11. If I look at master for tuesday for that city it is 11.
0
HURRAY!!!!!!!!!!!!! You are awesome. I made some mistake i think while copying. It works correctly.

THANK YOU...YOU ARE A CHAMPION
0
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 768
Feb 1, 2010 at 09:47 AM
Ok I just emailed your workbook too.
0