1

I'm currently struggling on finding the formula that will resolve my problem.

Here's the status quo:

  1. In Sheet 1, column A, I have a set of string, such as:

/search.action?gender=men&brand=10177&tag=10203&tag=10336
/search.action?gender=women&brand=11579&tag=10001&tag=10138
/search.action?gender=men&brand=12815&tag=10203&tag=10299
/search.action?gender=women&brand=1396&tag=10203&tag=10513
/search.action?gender=women&brand=11&tag=10001&tag=10073
/search.action?gender=women&brand=1396&tag=10203&tag=10336
/search.action?gender=women&brand=13

  1. In Sheet 2, column A, I have a set of strings such as:

brand=10177
brand=12815
brand=13
brand=1396
brand=11579

  1. Finally, in sheet 1, column B will be my "filter" with the formula I'm struggling to find. The goal of my formula is to detect in any of the strings in sheet 1 if one of the string in sheet 2 is present (as an exact match!). Indeed, now it only finds approximative matches. As you can see, the row 5 shouldn't return anything. But with my current formula it does.

Here's the formula:

{=IFERROR(INDEX('Sheet 2'!$A$1:$A$5;MATCH(1;COUNTIF(A1;"*"&'Sheet 2'!$A$1:$A$5&"*");0));"")}

Any idea on the matter?
Please note that I don't want to use VBA, macros, but only a formula.

Thanks a lot for your help!

BigBadBaptiste
  • 19
  • 6
  • It sounds as if you are putting the formula in sheet1 and searching sheet2, but if so I can't understand your formula. Can you clarify it a bit please? – Tom Sharpe Jan 20 '15 at 12:08
  • sure. Let's take it row by row. Formula is in B1. I want to look in A1 if any of the strings in the Sheet 2 is present, then display which one (in A1). – BigBadBaptiste Jan 20 '15 at 12:29
  • Do you mean Sheet1!B1 and Sheet1!A1? If so, my problem is that your formula doesn't reference Sheet2 anywhere so it can't compare the strings in A1 with the strings starting with "brand=10177" in Sheet2. – Tom Sharpe Jan 20 '15 at 12:54
  • oh dammit... indeed you're right... I edit my post... – BigBadBaptiste Jan 20 '15 at 12:57

2 Answers2

1

Following will solve your problem I guess:

=VLOOKUP(MID(A2,FIND("&",A2)+1,FIND("&",A2,FIND("&",A2)+1)-FIND("&",A2)-1),Sheet2!A:A,1,FALSE)

Basically with find function I have identified the start and length of the string in between "&" signs. and used in vlookup.

Another point to mention is this formula is only looking for the first 2 "&" signs.

Dubison
  • 750
  • 5
  • 12
0

For completeness, here is another solution based on this answer

=INDEX(Sheet2!$A$1:$A$5,MAX(IF(ISERROR(FIND(Sheet2!$A$1:$A$5,A1)),-1,1)*(ROW(Sheet2!$A$1:$A$5)-ROW(Sheet2!$A$1)+1)))

This is a bit more general and it doesn't matter how many search tags there are.

However as it stands it would match brand=13 in the second sheet with brand=1396 in the first sheet. To avoid that you could add an ampersand to the search strings:-

=INDEX(Sheet2!$A$1:$A$5,MAX(IF(ISERROR(FIND(Sheet2!$A$1:$A$5&"&",A1&"&")),-1,1)*(ROW(Sheet2!$A$1:$A$5)-ROW(Sheet2!$A$1)+1)))

This formula throws a #VALUE error if there is no match: to avoid this, you would need to put an IFERROR statement round it:-

=IFERROR(INDEX(Sheet2!$A$1:$A$5,MAX(IF(ISERROR(FIND(Sheet2!$A$1:$A$5&"&",A1&"&")),-1,1)*(ROW(Sheet2!$A$1:$A$5)-ROW(Sheet2!$A$1)+1))),"")

All these are array formulae.

enter image description here

Community
  • 1
  • 1
Tom Sharpe
  • 30,727
  • 5
  • 24
  • 37
  • Hi Tom, thanks for your input. I tried it out, however it doesn't give the expected result. The answer from Dubison resolved perfectly my problem. – BigBadBaptiste Jan 23 '15 at 14:11
  • Fine, the @Dubison answer is good and I have given it +1. I will include a screen shot though just to show that mine does work. – Tom Sharpe Jan 23 '15 at 17:03