Offset formula to different worksheet...

[edwardpestian]

I have the formula below, which takes the date from a cell(G8) and finds the corresponding data in another worksheet(Data!). This formula is working. But for some reason I can't get a second formula to look up the data one cell to the right.

=IF(Date=0,"",OFFSET(Data!$E$5,ROW(A1)+100,MATCH(G8,Data!$F$3:$CT$3,0)))

Any suggestions?

Regards,

EP

 

[xlbo]

please post the formula that ISN'T working

must say though that I don't really understand the one you have posted.

Surely ROW(A1)+100 is a constant = 101

which would make the formula:

=IF(Date=0,"",OFFSET(Data!$E$5,101,MATCH(G8,Data!$F$3:$CT$3,0)))


Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

Hi,

Arguments 4 & 5 of the OFFSET function that you posted are EMPTY. Arguments 4 & 5 define the number of rows & columns respectively, of the return array.

Skip,

_{[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount!}

 

[xlbo]

Skip - if they are left empty, I believe a 1*1 array (ie 1 cell) is used

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[edwardpestian]

I was able to get it working using the following forumula. Thanks all for your help.

EP

=IF(Date=0, "", OFFSET(Data!$E$5, ROW(A1)+100, MATCH(G8,
Data!$F$3:$CT$3, 0) + 1))

 

[edwardpestian]

Now I'd like to take it one step further and have the forumula match based on two different cell criteria: G8 and H8. Can I do this?

=IF(Date=0, "", OFFSET(Data!$E$5, ROW(A1)+100, MATCH(G8,
Data!$F$3:$CT$3, 0) + 1))

Thanks again.

EP

 

[SkipVought]

"Can I do this?"

To quote Bob The Builder, "Yes, we can!"

Skip,

_{[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount!}