I sure do appreciate all of the help. Especially Pete and Hank. You
guys just laid it out there and really took the time to help when you
did not have to; you did it cause that is the kind of guys you are, and
I really do appreciate that. Thank you.
You're welcome.
It's working out well, except for the #N/A. I cant seem to get rid of
that. I am using the exact formula that was posted here:
=VLOOKUP(F5,Sheet1!$A$2:$B$5,2,FALSE)
OK, I didn't cover N/A errors in my first post because I thought it
more important that you get a handle on the structure of the table,
and on using the VLookup function. Pete did cover it in in his post to
some extent.
You'll get the N/A error if the value that you're looking up (that is,
the first argument of the Vlookup formula) doesn't appear in the first
column of the table.
There could be a number of reasons for this.
- The first is (obviously) that the value that you're looking up
just doesn't appear in the first column.
- The second is if the value that you're looking up is stored as a
number in your table, but you're looking it up as text or vice versa.
(That could be a bit confusing. Let me explain it this way; if the
formula was, say, =VLOOKUP(75,Sheet1!$A$1:$B$7,2,FALSE), then the
NUMBER 75 must appear somewhere in the first column of the lookup
table. If instead you have 75 *formatted as text* in that table,
VLookup won't find it and will return N/A. Conversely, if you have the
function as =VLOOKUP("75",Sheet1!$A$1:$B$7,2,FALSE) (note the double
quotes around the 75 this time) and you have 75 formatted as a NUMBER
in the lookup table, again, Vlookup won't find it. I don't think this
applies in your case (so far) because all of your first column entries
appear to be alphanumeric (that is, they have both text AND numbers),
so they would have to be formatted as text. I'm only mentioning it in
case you come across this problem in the future.)
- The third possibility, which was mentioned by JG/Pinmaster, is if
the row containing the lookup value falls OUTSIDE the range that
you've specified as the table. I listed only the first 5 of your
entries and then said that you could spread it down as far as you
needed to. Pete was less lazy than me and specified all of the
combinations that you had mentioned in your original post. But in any
case, you DO need to make sure that the range name $A$2:$B$5 is
changed as needed to specify the WHOLE list. If you have 7 items,
it'll be $A$2:$B$8. If you have 10, it'll be $A$2:$B$11 and so on. (In
fact I'd usually start from row 1, not 2, even if the first row
contains headings only. That way if you insert a new row at the top of
the table (that is, in row 2), the VLookup formulas will automatically
change to include the new row.)
What people normally do when using a VLookup is to use an IsError or
IsNa function as well to prevent the N/A error from appearing. For
example:
=IF(ISNA(VLOOKUP(F5,Sheet1!$A$1:$B$7,2,FALSE)),"Not Found",
VLOOKUP(F5,Sheet1!$A$1:$B$7,2,FALSE))
That way it will show a more informative error message if, for
whatever reason, the value isn't there.