• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Index and Match for wrapped number

I

IKHAN

Member
Hello,

Having issues with index and Match for wrapped number in cell.

Using below formula and works fine for single ip in both input sheet and Data sheet.

=IF(ISNA(INDEX(Data!$B$2:$B$10,MATCH(input!$A2,Data!$A$2:$A$10,0))),"",INDEX(Data!$B$2:$B$10,MATCH(input!$A2,Data!$A$2:$A$10,0)))

If single ip in sheet(input) unable to match, if main sheet(DATA) has multiple ip address wrapped in a cell.

Any help will be appreciated. Attached sample file
 

Attachments

  • test.xlsx
    9.3 KB · Views: 14
Try,

In "input" sheet B1, formula copied across right to F1 and all copied down :

=IF(ISNUMBER(FIND($A1,Data!$A$1:$A$5509)),INDEX(Data!B$1:B$5509,MATCH("*"&$A1&"*",Data!$A$1:$A$5509,0)),"")

Regards
Bosco
 
When formula dragged down n across , if takes previous entries even thou column A(input sheet) is blank and it doesn't do the exact match.

Attached test file with formula provided
 

Attachments

  • test.xlsx
    9.9 KB · Views: 8
You must log in or register to reply here.
Back
Top