OK, I have a list created as a data validation list for people to fill in
blanks within a spreadsheet. List is below with the corresponding values for
each label:

ABSENT 0
CONCEPTUAL 1
APPLIED 2
EXPERT 3
INNOVATOR 4

I am using a formula to pull in the value associated with the label to a
selected cell. The formula is (assume the table is in A6:B10):

=IF(ISNA(VLOOKUP(F6,A6:B10,2)),"N/A",VLOOKUP(F6,A6:B10,2))

All works well except for the "Conceptual" selection pulls a "2" instead of
a "1". All the other selections work fine. Can someone explain this and
show me a fix?

thanks,
Scott

You should modify your 2 vlookups like this:
VLOOKUP(F6,A6:B10,2,FALSE)
--
** John C **
Please remember if your question is answered, to mark it answered :). It
helps everyone.


"sbickley" wrote:

OK, I have a list created as a data validation list for people to fill in
blanks within a spreadsheet. List is below with the corresponding values for
each label:

ABSENT 0
CONCEPTUAL 1
APPLIED 2
EXPERT 3
INNOVATOR 4

I am using a formula to pull in the value associated with the label to a
selected cell. The formula is (assume the table is in A6:B10):

=IF(ISNA(VLOOKUP(F6,A6:B10,2)),"N/A",VLOOKUP(F6,A6:B10,2))

All works well except for the "Conceptual" selection pulls a "2" instead of
a "1". All the other selections work fine. Can someone explain this and
show me a fix?

thanks,
Scott

Both Vlookup and match take an optional final argument with is the match
type. Vlookup expects a true or false for the final argument. True is the
default and that means that it is looking for the closest match in a
ascending list of options. False means taht an exact match is required. So
you need to add false as your 4th argument for your formula to work as it is
not sorted ascending.

=IF(ISNA(VLOOKUP(F6,A6:B10,2,false)),"N/A",VLOOKUP(F6,A6:B10,2, false))

match takes 0, 1 and -1 as the final argument. 0 is exact match. 1 is
ascending closest and -1 is descending closest... Index match is IMO a much
preferable formula...

--
HTH...

Jim Thomlinson


"sbickley" wrote:

OK, I have a list created as a data validation list for people to fill in
blanks within a spreadsheet. List is below with the corresponding values for
each label:

ABSENT 0
CONCEPTUAL 1
APPLIED 2
EXPERT 3
INNOVATOR 4

I am using a formula to pull in the value associated with the label to a
selected cell. The formula is (assume the table is in A6:B10):

=IF(ISNA(VLOOKUP(F6,A6:B10,2)),"N/A",VLOOKUP(F6,A6:B10,2))

All works well except for the "Conceptual" selection pulls a "2" instead of
a "1". All the other selections work fine. Can someone explain this and
show me a fix?

thanks,
Scott