Need help with ISNA formula syntax in Excel

Microsoft Microsoft excel 2007 full vers...
November 9, 2010 at 05:55:23
Specs: Windows XP
Hello, I know this is a simple one but I'm new to excel. Here is my formula:

=IF(E3="","",VLOOKUP(A7,INDIRECT(E3&"List"),2,0))

Cell A7 is blank, pending data from the user. I know I can sort this by using ISNA but I'm struggling with the syntax. Any help would be greatly appreciated.


See More: Need help with ISNA formula syntax in Excel

Report •

#1
November 9, 2010 at 06:14:45
Untested:

=IF(E3="","",IF(ISNA(VLOOKUP(A7,INDIRECT(E3&"List"),2,0)),"",VLOOKUP(A7,INDIRECT(E3&"List"),2,0)))

Note: I assume that the value in E3 is used to build a Range Name. If INDIRECT(E3&"List") doesn't evaluate to a known Range Name, then you are going to get a #REF error.

Although I don't really know what you are trying to do wth your spreadsheet, it appears that a Data Validation Drop Down in E3 might be a good idea. What I am basically suggesting is that you have some way of ensuring that the value in E3 is always a valid part of a Range Name.


Report •

#2
November 9, 2010 at 07:15:18
You're my hero mate! Cheers for that. It did the trick. I was pulling my hair out as I normally work with VBA in Access. I've occasionally used the IF isnull in Access but this simple little thing was bloody killing me. Thank again! :-)

Report •
Related Solutions


Ask Question