0

I want to retrieve the value in cell on active sheet if it matches the corresponding email column and has the keyword search. This formula is showing the error #VALUE!: 

IF(INDEX('DATA 1'!C:C;MATCH(OUTPUT!B13;'DATA 1'!A:A;0)="search");INDEX('DATA 1'!C:C;MATCH(B13;'DATA 1'!A:A;0));"Not available")
Ram
  • 3,092
  • 10
  • 40
  • 56
Mukund Jha
  • 5
  • 1
  • 4
  • No it has the email id. The other sheet "DATA1" has email ids with corresponding values - search, google display! I want to extract the value in active cell if they have any of them. – Mukund Jha Oct 21 '15 at 16:45

1 Answers1

0

You're missing a closed parenthesis on the first index/match:

=IF(INDEX('DATA 1'!C:C;MATCH(OUTPUT!B13;'DATA 1'!A:A;0))="search";INDEX('DATA 1'!C:C;MATCH(B13;'DATA 1'!A:A;0));"Not available")

If you want multiple keywords, then combine them with Or(), such as:

=If(Or(INDEX('DATA 1'!C:C;MATCH(OUTPUT!B13;'DATA 1'!A:A;0))="search",INDEX('DATA 1'!C:C;MATCH(OUTPUT!B13;'DATA 1'!A:A;0))="display"),INDEX('DATA 1'!C:C;MATCH(OUTPUT!B13;'DATA 1'!A:A;0)),"")

But maybe index/match isn't the most optimal way to do what you're trying to get after. Can you explain a little more (edit your OP), what you're trying to do so we can avoid an XY problem?

Community
  • 1
  • 1
BruceWayne
  • 22,923
  • 15
  • 65
  • 110
  • I want to perform this for multiple keywords say - "search","display" . =IF(INDEX('DATA 1'!C:C;MATCH(OUTPUT!B13;'DATA 1'!A:A;0))="search","google";INDEX('DATA 1'!C:C;MATCH(B13;'DATA 1'!A:A;0));"") it is still showing again the paranthesis error. – Mukund Jha Oct 21 '15 at 17:17
  • Also it is not returning the needed value witrh this formula. – Mukund Jha Oct 21 '15 at 17:25
  • @MukundJha - can you post some sample data/table in your OP? I've edited my post to show how you could look up two keywords (using `Or()`). – BruceWayne Oct 21 '15 at 17:37