I need to be able to use two colums (State (B2:C####), County(C2:C####)) to
match against corresponding columns 2 and 3 in a VLOOKUP (named FIPS_LOOKUP)
table and return data stored in Column 4 to WORKSHEET1!A2.
The idea is that a user will populate WORKSHEET1!Col B and C and the formula
pasted in Col a will return a value. The number of rows populated will
fluctuate widely, so I wul like to figure out how to hav eth formula run for
only the number of rows populated in Columns B and C.

You can use this kind of syntax:

=index(othersheet!$c$1:$c$100,
match(1,(a2=othersheet!$a$1:$a$100)*(b2=othersheet !$b$1:$b$100),0))
(one cell)

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can't use the whole column.


======
Maybe you can just show nothing if there's nothing in column A:

=if(a99="","",thatlongformulahere)



Nate wrote:

I need to be able to use two colums (State (B2:C####), County(C2:C####)) to
match against corresponding columns 2 and 3 in a VLOOKUP (named FIPS_LOOKUP)
table and return data stored in Column 4 to WORKSHEET1!A2.
The idea is that a user will populate WORKSHEET1!Col B and C and the formula
pasted in Col a will return a value. The number of rows populated will
fluctuate widely, so I wul like to figure out how to hav eth formula run for
only the number of rows populated in Columns B and C.

--

Dave Peterson

Thanks for the suggestion. I have been able to get the formula to return the
correct row in the lookup table, but I get #NA for the return value.

example array:
{=VLOOKUP(ST_COUNTY,MATCH(1,($B2=FIPS!A1:A3402)*(C $2=FIPS!B1:B3402),0),4,1)}

Whe ST_COUNTY is a defined table with range FIPS!$A$1:$D$3402
and Sheet 1 could have upto 35,000 records entered with repeating values.


Sheet one :
FIPS ST COUNTY
#N/A CO Boulder
CO Larimer
CO Weld
FL Miami-Dade
LA Jefferson Davis
LA Orleans

FIPS Table (ST_COUNTY):
STATE COUNTY FULLNAME FIPS
CO Adams Adams County 08001
CO Alamosa Alamosa County 08003
CO Arapahoe Arapahoe County 08005
CO Archuleta Archuleta County 08007
CO Baca Baca County 08009
CO Bent Bent County 08011
CO Boulder Boulder County 08013
CO Broomfield Broomfield County 08014

When the formula works correctly it should return a value in the FIPS column
to the FIPS Column in Sheet1. In this case it would be "08013"

any suggestions?


"Dave Peterson" wrote:


You can use this kind of syntax:

=index(othersheet!$c$1:$c$100,
match(1,(a2=othersheet!$a$1:$a$100)*(b2=othersheet !$b$1:$b$100),0))
(one cell)

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can't use the whole column.


======
Maybe you can just show nothing if there's nothing in column A:

=if(a99="","",thatlongformulahere)



Nate wrote:

I need to be able to use two colums (State (B2:C####), County(C2:C####)) to
match against corresponding columns 2 and 3 in a VLOOKUP (named FIPS_LOOKUP)
table and return data stored in Column 4 to WORKSHEET1!A2.
The idea is that a user will populate WORKSHEET1!Col B and C and the formula
pasted in Col a will return a value. The number of rows populated will
fluctuate widely, so I wul like to figure out how to hav eth formula run for
only the number of rows populated in Columns B and C.

--

Dave Peterson

You are not using INDEX as you were told

Try

=INDEX(FIPS!D1:D3402,MATCH(1,($B2=FIPS!A1:A3402)*( C$2=FIPS!B1:B3402),0))


entered with ctrl + shift & enter

since you indicated column 4 in your vlookup I assume it is the D column

--

Regards,

Peo Sjoblom


"Nate" wrote in message
...
Thanks for the suggestion. I have been able to get the formula to return
the
correct row in the lookup table, but I get #NA for the return value.

example array:

{=VLOOKUP(ST_COUNTY,MATCH(1,($B2=FIPS!A1:A3402)*(C $2=FIPS!B1:B3402),0),4,1)}

Whe ST_COUNTY is a defined table with range FIPS!$A$1:$D$3402
and Sheet 1 could have upto 35,000 records entered with repeating values.


Sheet one :
FIPS ST COUNTY
#N/A CO Boulder
CO Larimer
CO Weld
FL Miami-Dade
LA Jefferson Davis
LA Orleans

FIPS Table (ST_COUNTY):
STATE COUNTY FULLNAME FIPS
CO Adams Adams County 08001
CO Alamosa Alamosa County 08003
CO Arapahoe Arapahoe County 08005
CO Archuleta Archuleta County 08007
CO Baca Baca County 08009
CO Bent Bent County 08011
CO Boulder Boulder County 08013
CO Broomfield Broomfield County 08014

When the formula works correctly it should return a value in the FIPS
column
to the FIPS Column in Sheet1. In this case it would be "08013"

any suggestions?


"Dave Peterson" wrote:


You can use this kind of syntax:

=index(othersheet!$c$1:$c$100,
match(1,(a2=othersheet!$a$1:$a$100)*(b2=othersheet !$b$1:$b$100),0))
(one cell)

This is an array formula. Hit ctrl-shift-enter instead of enter. If
you do it
correctly, excel will wrap curly brackets {} around your formula.
(don't type
them yourself.)

Adjust the range to match--but you can't use the whole column.


======
Maybe you can just show nothing if there's nothing in column A:

=if(a99="","",thatlongformulahere)



Nate wrote:

I need to be able to use two colums (State (B2:C####),
County(C2:C####)) to
match against corresponding columns 2 and 3 in a VLOOKUP (named
FIPS_LOOKUP)
table and return data stored in Column 4 to WORKSHEET1!A2.
The idea is that a user will populate WORKSHEET1!Col B and C and the
formula
pasted in Col a will return a value. The number of rows populated
will
fluctuate widely, so I wul like to figure out how to hav eth formula
run for
only the number of rows populated in Columns B and C.

--

Dave Peterson