# Comparing value to master coloumn

Solved/Closed
albatros - Jan 31, 2010 at 01:28 PM
rizvisa1 Posts 4478 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???

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
Related:

## 7 responses

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

Thanks

Didn't find the answer you are looking for?

rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
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

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.