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

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,D1:D10})

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.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"SPStevo" wrote:

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

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"))

--
Biff
Microsoft Excel MVP


"SPStevo" wrote in message
...
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