Need help with a dynamic lookup and logical formula

[rwwkbw]

I'm trying to write a formula that would do the following between two sheets:

In Sheet 1 Reference the text in a cell - I'll call it B4

Look at at cells "Sheet2!$R$59:$FZ$69" (yes, I need an absolute reference because I intend to drag the formula) to see if a match to "Sheet1!B4" is in that range

Then, report the data that is found in the cell next it. (so if a match to "Sheet1!B4" is found in "Sheet2!R61" then report "Sheet2!R63 in the cell).

It strikes me as pretty easy, but I cannot get it to work. I'm trying to make quick work of an enormous sheet, but I cannot figure out what type of dynamic formula to write. Please help!

[RJH]

I think this addresses your question, though there may be a more elegant
solution that I'm not aware of:

http://office.microsoft.com/en-us/as...0corresponding

"rwwkbw" wrote:

>
> I'm trying to write a formula that would do the following between two
> sheets:
>
> In Sheet 1 Reference the text in a cell - I'll call it B4
>
> Look at at cells "Sheet2!$R$59:$FZ$69" (yes, I need an absolute
> reference because I intend to drag the formula) to see if a match to
> "Sheet1!B4" is in that range
>
> Then, report the data that is found in the cell next it. (so if a match
> to "Sheet1!B4" is found in "Sheet2!R61" then report "Sheet2!R63 in the
> cell).
>
> It strikes me as pretty easy, but I cannot get it to work. I'm trying
> to make quick work of an enormous sheet, but I cannot figure out what
> type of dynamic formula to write. Please help!
>
>
> --
> rwwkbw
> ------------------------------------------------------------------------
> rwwkbw's Profile: http://www.excelforum.com/member.php...o&userid=33002
> View this thread: http://www.excelforum.com/showthread...hreadid=528263
>
>

[JE McGimpsey]

Your problem statement is confusing to me, since R63 is not "next it"
from R61 in a way that I understand...

If you want to look up B4 in R59:R69, and return the value from the
corresponding cell in column S:

=VLOOKUP(Sheet1!B4,Sheet2!$R$59:$S$59, 2, FALSE)




In article <[email protected]>,
rwwkbw <[email protected]> wrote:

> I'm trying to write a formula that would do the following between two
> sheets:
>
> In Sheet 1 Reference the text in a cell - I'll call it B4
>
> Look at at cells "Sheet2!$R$59:$FZ$69" (yes, I need an absolute
> reference because I intend to drag the formula) to see if a match to
> "Sheet1!B4" is in that range
>
> Then, report the data that is found in the cell next it. (so if a match
> to "Sheet1!B4" is found in "Sheet2!R61" then report "Sheet2!R63 in the
> cell).
>
> It strikes me as pretty easy, but I cannot get it to work. I'm trying
> to make quick work of an enormous sheet, but I cannot figure out what
> type of dynamic formula to write. Please help!