Hi, I hope someone can give a little advice please.



What i am trying to attempt is to create a spreadsheet of a works rota that can populate a table of all employees off days within a calendar year. I am struggling to find a solution that enables me to populate a cell from a reference table using 2 reference points (a week and a day). The reference table has 3 columns. "week row", "day Number" and "job".

I have tried using vlookup to locate a cell in the "job" column but it doesn't work. How can i use a formula to first lookup a week (say week 3) and then in week 3 lookup any given day, lets say day 5 (which represents Thursday) and return a value of "403"?

Thanks in advance. It wouldn't surprise me if its a simple solution.

Attached Files

example 1.xlsx (8.6 KB, 10 views)

Take a look at the attached.

Attached Files

Week.Day.xlsx (13.2 KB, 12 views)

Great, works a treat and thanks for the real fast reply. Do you know how that formula can be converted into "numbers" on a mac by any chance? The ampersand character is combining the 2 values as one making them return a value of 35 which cannot be located instead of 3 & 5 as separate values. Again thanks for your reply

I am not quite sure what you are asking about.

I don't know even the most basic about Mac, but if it is the ampersand that is a problem you may be able to use the function CONCATENATE instead.

=INDEX(C3:C219,MATCH(CONCATENATE(D1,D2),INDEX(CONC ATENATE(A3:A219,B3:B219),),0))

If it is something else you mean, please explain.

There shouldn't be a space between C and A in the second CONCATENATE. That space is an error that sometimes appear in this forum.

Xor, you clearly know your marbles. CONCATENATE does the same thing, it makes the spreadsheet look for 35 in Columbia A and not 3 and 5 in different columns. But i can work with what you have already given me so thats great. thanks again.