Find and show with formula.

[asyamonique]

Good Day,
Is it possible that the formula will find specific number and will give the result as it is?
Its like if I wanted get from column A:A number 5 and if there is that number on that column it will type into the formulated cell as 5 if not it will keep blank.
Thanks

 

[etaf]

a vlookup or index/match

Index(A:A, Match(5,A:A,0))

But its better to use a range rather than A:A - so A1:A1000 or how ever many rows you have

You can out the 5 into a cell to lookup - say it was in cell C2 , and your range in A was A2 to A1000

Index(A2:A1000, Match(C2,A2:A1000,0))

Now any number you put in Cell C2 if it exists will be returned

and IFERROR will provide a blank cell if it cannot be found


=IFERROR(Index(A2:A1000, Match(C2,A2:A1000,0)),"")

 

[asyamonique]

Thanks a lot.

 

[jtakw]

Hi,

How about using COUNTIF?

Book3.xlsx
	A	B	C	D	E
1	1		5		5
2	2
3	5
4	7
5	6
Sheet934

Cell Formulas
Range	Formula
E1	E1	=IF(COUNTIF(A1:A100,C1),C1,"")

 

[Phuoc]

Or try

Book1
	A	B	C	D	E
1	9		5		5
2	2
3	5
4	7
5	1
Sheet1

Cell Formulas
Range	Formula
E1	E1	=IFERROR(VLOOKUP(C1,A:A,1,0),"")