Vba code or formula to search value in column and return the same

[harinsh]

Hi Mr.Excel,

I am looking for vba code or formula where I can search number in the given column (this column combination of numbers, text and special characters). In this I would like to take only number which I require or entire cell which contain the requested number, if provided number does not available I should get the not found message so, that it will help that requested number not presented in the column.

Can anyone help on this please? It's life saver for me....

Thank you,

 

[Aladin Akyurek]

Care to post 5 rows from a target column along with the desired result from that column?

 

[harinsh]

Yes sure, I could done it before, anyhow here below mentioned sample data

Column A
12365(CAN)
25526(WIN)
69856(Lost)
99865(NOW)*(NEW)
(Blank)65986
69859(Lost)**98768(New)

Require result : If I search for 99865 then I need that entire cell or row which contain that value.

please do let me know if anything need....

Thanks,

 

[Aladin Akyurek]

Thanks for the Excel readable sample in the forum itself...

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][/tr][tr][td]

1​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

2​

[/td][td]12365(CAN)[/td][td][/td][td]

99865​

[/td][td]99865(NOW)*(NEW)[/td][/tr]
[tr][td]

3​

[/td][td]25526(WIN)[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

4​

[/td][td]69856(Lost)[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

5​

[/td][td]99865(NOW)*(NEW)[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

6​

[/td][td](Blank)65986[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

7​

[/td][td]69859(Lost)**98768(New)[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

8​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

In D2 enter:

=LOOKUP(9.99999999999999E+307,SEARCH(C2,$A$2:$A$7),$A$2:$A$7)

 

[harinsh]

super....but even if I give blank or "0" value its searching and giving some values where I do not have any blanks here...can we control this...if search option blank means should not give value or should be NA...something like that...

Thanks for your help...

 

[Aladin Akyurek]

Lets extend it...

=IF(ISNUMBER(1/C2),LOOKUP(9.99999999999999E+307,SEARCH(C2,$A$2:$A$7),$A$2:$A$7),"NA")

 

[harinsh]

Thank you very much...I am really not sure whether I can learn this formula or understand this formula!!!

 

[Aladin Akyurek]

Thank you very much...

You are welcome.

I am really not sure whether I can learn this formula or understand this formula!!!

01. When C2 is empty:

1/C2 >> 1/0 >> #DIV/0!

02. When C2 houses a 0:

1/C2 >> 1/0 >> #DIV/0!

03. When C2 houses a string like DAX:

1/C2 >> 1/"DAX" >> #VALUE !

04. When C2 houses a non-zero number, say, 2:

1/C2 >> 1/2 >> 0.5

05.

ISNUMBER(1/C2) >> FALSE for [1] thru [3] and TRUE for [4]

06. When [5] is FALSE, IF yields N/A.

07. When [5] is TRUE, IF runs the LOOKUP bit.

08. Let's call 9.99999999999999E+307 BigNum, a limit of Excel itself. See for more: 9.9999999 .... ?

09. lookup(lookup-value,match-array,result-array)

returns a result from the result-array when looking for lookup-value in the match-array succeeds. A result from the result-array is positionwise a correlate of a hit in the match-array.

10. search(search-value,reference)

returns a position at which it finds the search value in some part of the reference. A position is always an integer.

11. LOOKUP(BigNum,SEARCH(C2,$A$2:$A$7),$A$2:$A$7) [where SEARCH(C2,$A$2:$A$7) == match-array and $A$2:$A$7 == result-array]

finds (if any) invariably the last integer (number) in the match-array SEARCH creates and returns a corresponding value from the result-array.

12. See for more involving [10] the following: https://www.mrexcel.com/forum/excel-questions/724843-there-case-sensitive-vlookup.html#post3568323

 

[harinsh]

Thanks again, let me check each and every points and try to understand myself on this...