Help with dynamic array lookup based on a single reference cell ..

[SPStevo]

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

 

[Luke M]

1. Setup a named range that is based off of your validated cell.
(Insert-name-Define) Define the range possibly as
=LOOKUP(A1,{"Apple","Banana","Citrus"},{B1:B10,C1:C10,D110})

Then in you cell's formula, instead of referring to A1, refer to the named
range.

2. SUMPRODUCT can do anything SUMIFS and COUNTIFS can do in 2007. I believe
=SUMPRODUCT(('Resource Requests'!B$3:B$100=$B3)*('Resource
Requests'!C$3:C$100<>"X"))

is close to what you're looking for.

 

[T. Valko]

Try these:

Q1:

VLOOKUP(E198,INDIRECT(A1),2,0)

Q2:

=SUMPRODUCT(--('Resource Requests'!B$3:B$100=$B3),--('Resource
Requests'!C$3:C$100<>"X"))