Why does my formula in Excell return a #N/A?

[Guest]

I am using the same formula on several different worksheets. Some work and
some return #N/A as a result. Any suggestions? Here is my formula:

=IF(ISERROR(VLOOKUP((CONCATENATE($C42,"INSQUOTE-AUTO")),Pronto2!$1:$2500,8,FALSE)),"",VLOOKUP((CONCATENATE($C42,"INSQUOTE-AUTO")),Pronto2!$1:$1940,8,FALSE))

 

[Guest]

Ok, the thing I notice is that you use 2 different ranges in your vlookups.
in the one where you are checking for an error, the range is $1:$2500 and in
the one where you are actually doing the lookup it is $1:$1940 so if you are
getting an #N/A error that would indicate that the value is in the range
1941:2500 but not in the range 1:1940. Also, if I could I would like to
point out if you are only trying to get rid of #N/A errors, you should use
the ISNA function instead. ISERROR includes all errors including #N/A, such
that you could be potentially masking an error that you should be dealing
with. And, FWIW, ISERR includes all errors except #N/A.

 

[Guest]

I just noticed, you are using entire rows for your lookup ranges. Isn't that
wasteful/doesn't it end up taking too long for calculations? I don't know,
because I just now tried it on a very small range and it worked. But your
ranges are fairly large.

 

[Guest]

Good eye. It was the range. Is there a way to unlimit the top range so I
don't run into this as my records grow?

 

[Peo Sjoblom]

He is using the whole sheet actually and beyond that unless he is using
excel 12, he could change that to
$1:$256 because that is what it is using excel 97 - 2003, if you type that
in the name box you'll see what I mean. If all the extra fluff is empty I
don't think it will matter much speed wise but it's not a good spreadsheet
design in general.


--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com

 

[Guest]

You could use a dynamic named range. Here are some examples that I have in
one of my spreadsheets:

JobTitle =OFFSET(Applicants!$P$3,,,COUNTA(Applicants!$P:$P)-1
sort_area =OFFSET(Applicants!$A$2,,,COUNTA(Applicants!$A:$A),COUNTA(Applicants!$2:$2))
Store =OFFSET(Applicants!$L$3,,,COUNTA(Applicants!$L:$L)-1)
UpdDate =OFFSET(Applicants!$A$3,,,COUNTA(Applicants!$A:$A)-1)

That way, as the range grows, the named area that the named range refers to,
expands. Note, this would assume no interspersed blank cells.

 

[Guest]

I see what you are saying when I enter that into the name box. However, I
used a much smaller range when I was testing:
=VLOOKUP(H1,$4:$29, 6, FALSE)
and it appeared to be using entire rows from row 4 through row 29. Let me
check again (by hitting F2 and seeing what Excel "colors" as the range being
used.
Yes, rows 4 - 29 columns A through IV is the highlighted range.