Data validation query

  • Thread starter Thread starter Flub
  • Start date Start date
F

Flub

Hi All
I have a work sheet best decribed as a timesheet.
I also have two columns e.g. A and B containing
Names (A) and Pay numbers (B).In Cell C1 I have
a Data validation drop down list containing the names,
in D1 I would like the corresponding Pay number to
be automatically entered when selecting a name from
the drop down list.I hope this is clearer than mud (lol).
Thanks for any and all help
Regards
Glenn
 
Use a vlookup formula in D1, something like this

=IF(ISNUMBER(MATCH(C1,A1:A500,0)),VLOOKUP(C1,A1:B500,2,0),"")
 
In D1:

=VLOOKUP(C1,A:B,2,0)

HTH
Jason
Atlanta, GA
Click to expand...
Hi Jason
Is there a way for Excel to ignore blanks in Col C
so that it doesn't display #N/A
Thanks and Regards
Glenn
 
See Peo's formula or use:

=IF(COUNTIF(A:A,C1),VLOOKUP(C1,A:B,2,0),"")

HTH
Jason
Atlanta, GA
 
Just a heads up Jason, I used to use the if(countif method until I realized
that countif
is very forgiving. For instance, if by some chance you have text numbers in
the vlookup
table and a numeric value as the lookup value, then countif will still
return TRUE while the vlookup
will return #N/A while isnumber(match will not return a number. So if that
is the case (even if the chance might be low)
the auditing will be harder since the 2 options should be a value or a
blank..
 
Back
Top