Finding Cell Outside Of Range
 

Greetings,

I am trying to get the value from a cell just outside of a named
range.

This is my formula, it returns the invoice number (column 1):

=VLOOKUP(rAInvNo,rInv,1,FALSE)

rInv is the range C3:DU839 on the "Input" sheet
rAInvNo is the Invoice number on the "Invoice" sheet - which comes
from column C on the "Input" sheet (the first column in the range
rInv).

The cell I am interested in is column B in the same row as the match
to the value of rAInvNo which is in column C.

I tried to expand the range rInv from C3:DU839 the B3:DU839 - Didn't
work at all. Aside from the fact that all of the formulas referencing
rInv returned the #N/A error (I would think that since all I did was
add a column to the front of the list, that they would all just return
the value of the cell next to the one that was requested - Not so!!!)

I tried to use this formula as the reference for OFFSET, but debug
would not accept it.

Anyone have any ideas or suggestions?

Any help will be appreciated.

-Minitman

Finding Cell Outside Of Range
 

Try this:

=INDEX(Input!B3:B839,MATCH(rAInvNo,INDEX(rInv,,1), 0))

--
Biff
Microsoft Excel MVP


"Minitman" wrote in message
...
Greetings,

I am trying to get the value from a cell just outside of a named
range.

This is my formula, it returns the invoice number (column 1):

=VLOOKUP(rAInvNo,rInv,1,FALSE)

rInv is the range C3:DU839 on the "Input" sheet
rAInvNo is the Invoice number on the "Invoice" sheet - which comes
from column C on the "Input" sheet (the first column in the range
rInv).

The cell I am interested in is column B in the same row as the match
to the value of rAInvNo which is in column C.

I tried to expand the range rInv from C3:DU839 the B3:DU839 - Didn't
work at all. Aside from the fact that all of the formulas referencing
rInv returned the #N/A error (I would think that since all I did was
add a column to the front of the list, that they would all just return
the value of the cell next to the one that was requested - Not so!!!)

I tried to use this formula as the reference for OFFSET, but debug
would not accept it.

Anyone have any ideas or suggestions?

Any help will be appreciated.

-Minitman

Finding Cell Outside Of Range
 

Thanks Biff.

That works great!!!

-Minitman


On Tue, 15 Apr 2008 00:17:30 -0400, "T. Valko"
wrote:

Try this:

=INDEX(Input!B3:B839,MATCH(rAInvNo,INDEX(rInv,,1) ,0))

Finding Cell Outside Of Range
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Minitman" wrote in message
...
Thanks Biff.

That works great!!!

-Minitman


On Tue, 15 Apr 2008 00:17:30 -0400, "T. Valko"
wrote:

Try this:

=INDEX(Input!B3:B839,MATCH(rAInvNo,INDEX(rInv,,1 ),0))