CODEWITHSUNDEEP
excelvbaexcel-formula
martian_hunter
martian_hunter

Reputation: 11

MATCH lookup values in multiple columns

I've a table with multiple columns of the attribute IP addresse. I want to search for the IP address in all the column, say B to F, and if the value is found in any of these column, return that row index.

I've tried MATCH but it seem to be able to only search in single column and not the range of columns that I give it.

=MATCH(CONCATENATE("*",TRIM(A1)),machine_name!$B$1:$F$288,0)

Sample Table

Upvotes: 1

Views: 143

Answers (1)

user4039065
user4039065

Reputation:

To return the row number of a wildcard match to a partial IP in A1 try,

'for 192.168.0.*
=AGGREGATE(15, 7, ROW($1:$288)/(LEFT($B$1:$F$288, LEN(A1))=A1), 1)
'for *.168.0.10
=AGGREGATE(15, 7, ROW($1:$288)/(RIGHT($B$1:$F$288, LEN(A1))=A1), 1)

Upvotes: 1

Related Questions