Data lookups

**shutchinson** · 04-16-2011, 05:16 PM

Hi guys,

looking for some help, I have attached a file so that you can easier see the problem i face, well for me a problem, for some of you guys n gals maybe not.

there are two sheets, one is a time sheet,. the other contains data. what i want to happen is this

Columns I,J and K to auto populate with the basic, lower and higher hours using the data in sheet 2

in the data sheet column A = day and col b = the shift pattern
in the time sheet column B = day and col e = the shift pattern

i want to somehow lookup the day and the shift pattern and auto fill the relevant basic, lower and higher columns

Not sure if any of that makes sense, but if so is anyone able to pose a solution please.

help much appreciated

**MarvinP** · 04-16-2011, 06:14 PM

Hi shutchinson,

No problem.
Create a index on the data sheet and a vlookup on the time sheet. See attached. say thanks,

If your mac version doesn't understand IfError, let me try again.

**shutchinson** · 04-17-2011, 06:03 PM

Hi MarvinP,

that works great, thank you. not great with excel, could you explain if you have the time how it works please.

**MarvinP** · 04-17-2011, 06:23 PM

OK - here's the story,

On the Data Cells sheet I inserted a blank column A and did a formula there.
This formula said to stick the Day and Times together in a single Cell. I wanted this to do a VLookup on the Front Sheet. The formula in A3 and down is:

Please Login or Register  to view this content.

This is called a "building an index" type of formula.

Now on the Front sheet I want to look up the Basic, Higher or Lower numbers for each row. In the row is a Day and Time. So the formula in I10 does this.
It takes the Day and Time in B5 and E5 and puts them together as the INDEX did above. I use this combined index and go looking on the Data Cells sheet for a VLookup match. When I find the correct row, where the indexes match, I return the number of rows to the right. For Basic it is 6 columns to the right and 5 for Higher and 4 for Lower. See the formulas in Row 10 columns I, J and K.

Then when I pulled it down it gave some #N/A stuff because it couldn't find the index in the table on Data Cells Sheet. I added the IFERROR part of the formula to return a blank instead of an error answer.

hope that helps.