Hello,

I'm trying to find a way to reference one of multiple arrays from a single
reference cell for a VLOOKUP. For example, the formula I'm using is:

=IF(E198<"",IF(ISERROR(VLOOKUP(E198,$A$4:$C$191,2 ,FALSE)),"X",VLOOKUP(E198,$A$4:$C$191,2,FALSE)),"" )

Rather than use the fixed range $A$4:$C$191, I'd like to give it a name and
be able to tell all cells that call this function to change their lookup
based on a single cell.

For example, I have 3 arrays: Apple, Banana, Citrus. I would like to create
a data validated cell with those three selections and then have all the
VLOOKUPs reference the named range specified by those data.

So, if the data validated cell is A1, I would expect my formula to resemble
=IF(E198<"",IF(ISERROR(VLOOKUP(E198,A1,2,FALSE)), "X",VLOOKUP(E198,A1,2,FALSE)),"")
.... but this doesn't work because excel doesn't recognize "Banana" in A1 as
the named range Banana.

Is there any way to perform this more dynamic formula building?

A second question, if I might. I'm referencing data on another sheet via
=COUNTIF('Resource Requests'!B$3:B$100,$B3). I don't have Excel 2007
available on this computer so I can't use the COUNTIFS function. The problem
is that I only want to count those entries for rows that don't include value
"X" in an adjacent column. What suggestions do you have?

Many thanks,
Steve