finding numbers

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Is it possiable to find multible numbers on one sheet with vlook up? At work
we have 30 people in a lottery group and there are 5 lines of numbers per
person at 6 numbers per line. trying to make this easyer to do. any help
would be appreciated.
Thanks , Henry
 
Henry,

Let's say that you put the winning numbers into cells B1:G1, and that your
list of guessed numbers start in row 2, column B to G, and go down the
sheet. In cell H2, use the formula

=SUMPRODUCT(--NOT(ISERROR(MATCH(B2:G2,$B$1:$G$1,FALSE))))

and copy down as far as you need. This will list the number of matches in
each set of numbers. You can sort (descending) or filter based on that
column to show the higher number of matches.

If you want to see the matches highlighted, select all your numbers (in
cells B2:G???) and then select Format | Conditional Formatting... Use
"Formula Is..." and the formula

=NOT(ISERROR(MATCH(B2,$B$1:$G$1,FALSE)))

Set the pattern of the format to whatever color you want to highlight the
actual matches.

HTH,
Bernie
MS Excel MVP
 
Thank you
Henry

Bernie Deitrick said:
Henry,

Let's say that you put the winning numbers into cells B1:G1, and that your
list of guessed numbers start in row 2, column B to G, and go down the
sheet. In cell H2, use the formula

=SUMPRODUCT(--NOT(ISERROR(MATCH(B2:G2,$B$1:$G$1,FALSE))))

and copy down as far as you need. This will list the number of matches in
each set of numbers. You can sort (descending) or filter based on that
column to show the higher number of matches.

If you want to see the matches highlighted, select all your numbers (in
cells B2:G???) and then select Format | Conditional Formatting... Use
"Formula Is..." and the formula

=NOT(ISERROR(MATCH(B2,$B$1:$G$1,FALSE)))

Set the pattern of the format to whatever color you want to highlight the
actual matches.

HTH,
Bernie
MS Excel MVP
Click to expand...
 
Back
Top