Another nesting issue

[Ed Davis]

Does anyone know how I can get information from a range of cells more than
nine rows?
Example
If a name shows up within a range from range D9 through D20 then enter
whatever
is in the cell in column F but the same row as the name shows up on?

 

[Don Guillett]

Look in the help index for MATCH. Then look for INDEX and nest your match
within the index.

 

[Ed Davis]

Sorry I do not understand what I need to do here.

Thanks

 

[Don Guillett]

Are you saying that you don't know how to use Excel help?

 

[Ed Davis]

OK I figured it out but I now have another problem.
This is the formula I am using.

=INDEX(C$6:F$14,MATCH(C34,$C$6:$C$14,0),3)

However, if the name in c34 does not exist it gives me the following:
#N/A

Is there a way to not get the N/A message?


Thanks in advance.

 

[Pete_UK]

Try this:

=IF(ISNA(MATCH(C34,$C$6:$C$14,0)),"",INDEX(C$6:F$14,MATCH(C34,$C$6:$C
$14,0),3))

Gives you a blank cell instead - you could change the "" to "warning"
or some-such.

Hope this helps.

Pete

 

[Ed Davis]

That did it.
Thank you very much.


Try this:

=IF(ISNA(MATCH(C34,$C$6:$C$14,0)),"",INDEX(C$6:F$14,MATCH(C34,$C$6:$C
$14,0),3))

Gives you a blank cell instead - you could change the "" to "warning"
or some-such.

Hope this helps.

Pete

 

[Pete_UK]

You're welcome, Ed - thanks for feeding back.

Pete