Finding Cell Outside Of Range

[Minitman]

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 C3U839 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 C3U839 the B3U839 - 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

 

[T. Valko]

Try this:

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

 

[Minitman]

Thanks Biff.

That works great!!!

-Minitman

 

[T. Valko]

You're welcome. Thanks for the feedback!