Lookup in named range

[Guest]

Afternoon

Im currently creating a lookup formula between two worksheets, and would
find it easier to make my formula search within name ranges rather than cell
references. Im having problems making this work though. My current formula
reads.
=INDEX('Communal Areas'!A2:H804,MATCH(E2,'Communal Areas'!D2804,0),7)
The name range for A2:H804 is Communal, and the name range for D2804 is
Street. When I enter these though my formula comes back in error. I assume
then that I am entering them into the formula incorrectly. Does anyone know
how this should be done?
Thanks in advance.

J.

 

[Jim Rech]

I set up your scenario and then applied the range names. This formula
worked fine:

=INDEX(Communal,MATCH(E2,Street,0),7)

Incidentally, it is more efficient to narrow the ranges that INDEXes,
MATCHes, VLOOKUPs, etc., use as much as possible. So since you know all
you're interested in is the seventh column for the INDEX you'd be better off
using INDEX just on that column:

=INDEX('Communal Areas'!G2:G804,MATCH(E2,'Communal Areas'!D2804,0))


--
Jim
| Afternoon
|
| Im currently creating a lookup formula between two worksheets, and would
| find it easier to make my formula search within name ranges rather than
cell
| references. Im having problems making this work though. My current
formula
| reads.
| =INDEX('Communal Areas'!A2:H804,MATCH(E2,'Communal Areas'!D2804,0),7)
| The name range for A2:H804 is Communal, and the name range for D2804 is
| Street. When I enter these though my formula comes back in error. I
assume
| then that I am entering them into the formula incorrectly. Does anyone
know
| how this should be done?
| Thanks in advance.
|
| J.

 

[Dave Peterson]

Are these names workbook level or local to the sheet?

If they're global, then this worked ok for me:
=INDEX(Communal,MATCH(E2,street,0),7)

If they're local to the 'communal areas' worksheet, then this worked ok:

=INDEX('Communal areas'!communal,MATCH(E2,'Communal areas'!street,0),7)

You may want to post the formula you used--or even double check the range names
(make sure that they're what they're supposed to be).

 

[JE McGimpsey]

This works for me:

=INDEX(Communal,MATCH(E2,Street,0),7)

without knowing what error you're getting, it's hard to diagnose what
might be going wrong...