EXCEL FORMULA

[Guest]

Help please!

I have a worksheet "receiving data" column1 state; column 2 city county
name; column 3 gross (blank); column 4 tax(blank). I have a "data worksheet"
cities listed in column 1; county name in column 2; state in column 3; gross
amount column 4; tax amount column 5; there is a county totals after each
county. State total at the bottom

I need to look for the city county listed shown on the "receiving sheet" in
the "data worksheet" match the names then place the gross and tax info form
column 4 & 5 from the "data" into the receiving sheet.

I filtered just the county names and range named them; I did the same with
city. Now, I do not think that is correct because the forumla cannot match
the city with county. There could be 2 cities with the same name but be in
different counties.

I used Vlookup but it if did not find the city/county it returned an N/A.
I cannot total the columns when N/A is present.

How can I change the N/A to blank or zero? Should I range name the city and
county in the format the are currently in?

 

[JulieD]

Hi

the 'problem' as i'm reading it is that in 'receiving data' you have city &
county in one cell, while in 'data worksheet' you have them in different
columns ... therefore, my suggestion would be to insert a new column in
'data worksheet' (which can be hidden) as column C (or A if you really want)
and concatenate the city & county into that cell (=A1 & " " & B1) and use
that as the first column of your table to lookup to.

additionally, i would remove the subtotals from the 'data worksheet' and use
data / sort & then data / subtotals when you need to see the subtotals.

Hope this helps
Cheers
JulieD

 

[Guest]

Hi Thanks, Unfortunately, the "data" is transformed from the main frame and
it is volumnious. Data changes each month but the format is always the same
The current vlook is working I just can't get the N/A to be blank when the
city/county cannot be found.

 

[JulieD]

Hi

in that case try the following

=IF(ISNA(your_vlookup_formula),"",your_vlookup_formula)

Cheers
JulieD

 

[Guest]

I can't seem to get it to work right this is a more advance formula than I am
use to working with. My formula is:

-Vlookup("totals for county: autauga", alavlookupbycounty,4,false)

how and where do I incorporate the IF ISNA?

 

[JE McGimpsey]

One way:

=IF(ISNA(VLOOKUP("totals for county: autauga",
alavlookupbycounty,4,false)), "", VLOOKUP("totals for county: autauga",
alavlookupbycounty,4,false))

 

[Guest]

fantastic!!!!

thank you so much!

Hi Thanks, Unfortunately, the "data" is transformed from the main frame and
it is volumnious. Data changes each month but the format is always the same
The current vlook is working I just can't get the N/A to be blank when the
city/county cannot be found.