Excel Nested IF INDEX MATCH Function

0 votes

Based on the rows in which column B:B equals a certain number, I need to determine whether or not specific strings exist in either of two columns. If one of the two columns is detected, I want it to return "rejected."

At first, I had only one column to search, so I used a straightforward Search Index Match Formular embedded within an if Statement. This operates without a hitch.

=if(SEARCH({"neg", "negativ", "negative", "negatives", "Bonität"},index(Input_Jira!$B$1:$N$2000,MATCH("*"&G90&"*",Input_Jira!B:B,0),2)),"rejected","")

To include the second column I added the same if search index match logic for the case the first if statement is false:

=if(SEARCH({"neg", "negativ", "negative", "negatives", "Bonität"}, index(Input_Jira!$B$1:$N$2000,MATCH("*"&G90&"*",Input_Jira!B:B,0),13)),"rejected",if(SEARCH({"neg", "negativ", "negative", "negatives", "Bonität"},index(Input_Jira!$B$1:$N$2000,MATCH("*"&G90&"*",Input_Jira!B:B,0),2)),"rejected",""))  

This function gives me a value error although the separate part of the functions works perfectly fine.

Apr 1, 2023 in Others by Kithuzzz
• 38,010 points
641 views

1 answer to this question.

0 votes

Use COUNTIF and an array if all you need to do is count the number of times a set of strings appears in a range:

enter image description here

E2 = =SUM(COUNTIF($A$1:$B$12;{"a","b"}))

E3 = =SUM(COUNTIF($A$1:$B$12;{"e";"f"}))

E4 = =SUM(COUNTIF($A$1:$B$12;{"a";"b"}))

If the result is zero, there is absolutely no match (Second case). This can be used in conjunction with an IF statement to accomplish any task. Moreover, COUNTIF can be applied to wildcars if you just need partial matches.

answered Apr 1, 2023 by narikkadan
• 63,420 points

Related Questions In Others

0 votes
1 answer

Excel worksheet multi-criteria function (like Index-Match array) to fetch last value of an item, looking up by item name and date

Use: =SUMIFS(C:C,B:B,E12,A:A,MAXIFS(A:A,B:B,E12)) It will return the value at the ...READ MORE

answered Apr 6, 2023 in Others by narikkadan
• 63,420 points
431 views
0 votes
1 answer

IF function in combination with an Round function Excel

I'm not sure if it is a ...READ MORE

answered Sep 25, 2022 in Others by narikkadan
• 63,420 points
2,506 views
0 votes
1 answer

Excel Formula multiple Index Match and Average the result

If the conditions are separate and unrelated, ...READ MORE

answered Oct 31, 2022 in Others by narikkadan
• 63,420 points
2,642 views
0 votes
1 answer

Excel formula that combines MATCH, INDEX and OFFSET

Try this: =INDEX((OFFSET(SheetA!F:F,0,SheetA!A1)),MATCH(SheetB!C4, ...READ MORE

answered Nov 7, 2022 in Others by narikkadan
• 63,420 points
1,505 views
0 votes
1 answer

Excel Formula - if values in columns all contain X then return

You can use the AND function: = IF(AND(A3="OK";B3="OK";C3="OK");"everything ...READ MORE

answered Nov 4, 2022 in Others by narikkadan
• 63,420 points
1,148 views
0 votes
1 answer

(Excel) If cell is greater than <condition> then minus <number>

The IF function to calculate B5 (amount ...READ MORE

answered Nov 7, 2022 in Others by narikkadan
• 63,420 points
782 views
0 votes
1 answer

Multiple IF statements in Excel not working

When you add an IF statement to a formula ...READ MORE

answered Nov 12, 2022 in Mobile Development by narikkadan
• 63,420 points
250 views
0 votes
1 answer

IfError with else, does this function exist in Excel?

Next to IFERROR(), there also is the ISERROR() function, which ...READ MORE

answered Dec 23, 2022 in Others by narikkadan
• 63,420 points
311 views
0 votes
1 answer

Excel - IF combined with Index Match

Try: =IFERROR(IF(ISNUMBER(O2),VLOOKUP(A2,Sheet2!$C$2:$E$10,3,FALSE),""),"") =IFERROR(IF(ISNUMBER(O2),INDEX(Sheet2!$E$2:$E$10,MATCH(A2,Sheet2!$C$2:$C$10,0)),""),"") where the first "" means that O2 is not a number, ...READ MORE

answered Nov 17, 2022 in Others by narikkadan
• 63,420 points
481 views
0 votes
1 answer

Excel Count if with index match

Try this: Countif(Index(rangeoflookupvalues,,match(Columnvaluetobe ...READ MORE

answered Dec 10, 2022 in Others by narikkadan
• 63,420 points
1,471 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP