Ask a question »

Excel - Check a specific value within a range

August 2015


[Excel] Check a specific value within a range



Issue


I need a formula that can check a specific value in a range of values and show the cell location for each time the value is find

Suppose Serial No is in column A1 to A20, and data range is in B1 to B20.

Solution


There are many ways of doing it
  • Highlight B1 t0 B20
  • Edit -find(or control +F).

Against find what type
40
  • And click findall

you will get below find dialog box
B2,B5,Bo

that means serial nos. are 2,5,9
  • Another way:
    • In C1 or in any empty cell in that row 1 type this formula:

=IF(B1=40,A1,"") 
  • Copy C1 down up to C20 and see what happens.

Note


Thanks to venkat1926 for this tip on the forum.
For unlimited offline reading, you can download this article for free in PDF format:
Excel-check-a-specific-value-within-a-range.pdf

See also

In the same category

Published by aakai1056.
This document entitled « Excel - Check a specific value within a range  » from CCM (ccm.net) is made available under the Creative Commons license. You can copy, modify copies of this page, under the conditions stipulated by the license, as this note appears clearly.