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

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

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

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

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

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