I am having the following problem with VLOOKUP:

I have a cell linked to a combo box that chooses from 23 values, 21 of which
are numerical, and 2 which are letters. Linking the cell is easy, but when I
try and use the linked cell value in a VLOOKUP, it only works for the letter
choices. The numbers give me a #NA error. I am sure all the ranges are
good, and my list is even sorted in ascending order. i thought VLOOKUP was
giving me a hard time because the numbers were in "General" format, so I
converted them using TEXT, but still no joy. Please help.

wazooli

Are you using the fourth argument on the VLOOKUP? eg
=VLOOKUP(A1,AD!:AF200,2,FALSE)
=VLOOKUP(FindWhat,LookWhere?,WhichColumn?,FindExac tMatch)

"Wazooli" wrote in message
...
I am having the following problem with VLOOKUP:

I have a cell linked to a combo box that chooses from 23 values, 21 of
which
are numerical, and 2 which are letters. Linking the cell is easy, but
when I
try and use the linked cell value in a VLOOKUP, it only works for the
letter
choices. The numbers give me a #NA error. I am sure all the ranges are
good, and my list is even sorted in ascending order. i thought VLOOKUP
was
giving me a hard time because the numbers were in "General" format, so I
converted them using TEXT, but still no joy. Please help.

wazooli

=VLOOKUP(A1,AD1:AF200,2,FALSE)
sorry!


"Alan" wrote in message
...
Are you using the fourth argument on the VLOOKUP? eg
=VLOOKUP(A1,AD!:AF200,2,FALSE)
=VLOOKUP(FindWhat,LookWhere?,WhichColumn?,FindExac tMatch)

"Wazooli" wrote in message
...
I am having the following problem with VLOOKUP:

I have a cell linked to a combo box that chooses from 23 values, 21 of
which
are numerical, and 2 which are letters. Linking the cell is easy, but
when I
try and use the linked cell value in a VLOOKUP, it only works for the
letter
choices. The numbers give me a #NA error. I am sure all the ranges are
good, and my list is even sorted in ascending order. i thought VLOOKUP
was
giving me a hard time because the numbers were in "General" format, so I
converted them using TEXT, but still no joy. Please help.

wazooli

Indeed I am. Here is my actual formula:

=VLOOKUP(Chr_choice,table,5,FALSE),

where Chr_choice is a cell linked to my combo box that is populated from a
subtable of table. Table has 7 columns, with the fifth being the start
address I want. I used ADDRESS to populate the table according to
Chr_choice. My VLOOKUP formula works great if the choices are "X", "Y", or
"All", but fails for chromosomes 1-22. In addition, if I hard code the
chromosome number into the VLOOKUP formula, it works. It seems to be having
a problem with the fact the Chr_choice is linked to a combo box.

"Alan" wrote:

Are you using the fourth argument on the VLOOKUP? eg
=VLOOKUP(A1,AD!:AF200,2,FALSE)
=VLOOKUP(FindWhat,LookWhere?,WhichColumn?,FindExac tMatch)

"Wazooli" wrote in message
...
I am having the following problem with VLOOKUP:

I have a cell linked to a combo box that chooses from 23 values, 21 of
which
are numerical, and 2 which are letters. Linking the cell is easy, but
when I
try and use the linked cell value in a VLOOKUP, it only works for the
letter
choices. The numbers give me a #NA error. I am sure all the ranges are
good, and my list is even sorted in ascending order. i thought VLOOKUP
was
giving me a hard time because the numbers were in "General" format, so I
converted them using TEXT, but still no joy. Please help.

wazooli

It should work, I've used comboboxes in VLOOKUPS lots of times without any
problems. The fact that it works if you hard code it leads me to believe
that there is a space or something included. Is the information coming from
a web site? If so it may contain the ASCII character 160 which looks like a
space but isn't.Try Edit Replace, hold down the left Alt key and type in
0160, (nothing will show in the dialogue box) and go replace all. If that
doesn't work send me the file if you want to, I'm curious!


"Wazooli" wrote in message
...
Indeed I am. Here is my actual formula:

=VLOOKUP(Chr_choice,table,5,FALSE),

where Chr_choice is a cell linked to my combo box that is populated from a
subtable of table. Table has 7 columns, with the fifth being the start
address I want. I used ADDRESS to populate the table according to
Chr_choice. My VLOOKUP formula works great if the choices are "X", "Y",
or
"All", but fails for chromosomes 1-22. In addition, if I hard code the
chromosome number into the VLOOKUP formula, it works. It seems to be
having
a problem with the fact the Chr_choice is linked to a combo box.

"Alan" wrote:

Are you using the fourth argument on the VLOOKUP? eg
=VLOOKUP(A1,AD!:AF200,2,FALSE)
=VLOOKUP(FindWhat,LookWhere?,WhichColumn?,FindExac tMatch)

"Wazooli" wrote in message
...
I am having the following problem with VLOOKUP:

I have a cell linked to a combo box that chooses from 23 values, 21 of
which
are numerical, and 2 which are letters. Linking the cell is easy, but
when I
try and use the linked cell value in a VLOOKUP, it only works for the
letter
choices. The numbers give me a #NA error. I am sure all the ranges
are
good, and my list is even sorted in ascending order. i thought VLOOKUP
was
giving me a hard time because the numbers were in "General" format, so
I
converted them using TEXT, but still no joy. Please help.

wazooli

Sounds like you are trying to lookup the string "123" and the table contains
the number 123. This doesn't match.

You need to convert you string to a number

=vlookup(1*Chr_choice,Table,5,False)

although if it is a mix of numbers and letters which will be looked up, you
need to use an if statement - perhaps do two lookups and use the one not
returing an error.

--
Regards,
Tom Ogilvy

"Wazooli" wrote in message
...
Indeed I am. Here is my actual formula:

=VLOOKUP(Chr_choice,table,5,FALSE),

where Chr_choice is a cell linked to my combo box that is populated from a
subtable of table. Table has 7 columns, with the fifth being the start
address I want. I used ADDRESS to populate the table according to
Chr_choice. My VLOOKUP formula works great if the choices are "X", "Y",
or
"All", but fails for chromosomes 1-22. In addition, if I hard code the
chromosome number into the VLOOKUP formula, it works. It seems to be
having
a problem with the fact the Chr_choice is linked to a combo box.

"Alan" wrote:

Are you using the fourth argument on the VLOOKUP? eg
=VLOOKUP(A1,AD!:AF200,2,FALSE)
=VLOOKUP(FindWhat,LookWhere?,WhichColumn?,FindExac tMatch)

"Wazooli" wrote in message
...
I am having the following problem with VLOOKUP:

I have a cell linked to a combo box that chooses from 23 values, 21 of
which
are numerical, and 2 which are letters. Linking the cell is easy, but
when I
try and use the linked cell value in a VLOOKUP, it only works for the
letter
choices. The numbers give me a #NA error. I am sure all the ranges
are
good, and my list is even sorted in ascending order. i thought
VLOOKUP
was
giving me a hard time because the numbers were in "General" format, so
I
converted them using TEXT, but still no joy. Please help.

wazooli