Alternate to IFNA for my INDEX MATCH formula

Question

Pretty new to this level of formula and coding, and am learning on the go. I recently got some help to make an INDEX MATCH formula work with an IFNA statement. It works perfectly now (Link below, "Session 1" Sheet, Cells E14-E23)

I tried to use the same style of formula in E29-E38 (highlighted in orange). However due to requiring multiple IF statements, the IFNA isn't allowing it to work now.

I need to search and match what is in cells D29-38 of "Session 1" sheet to where it is listed in column A of varying other sheets (you can see which sheets in the code in E29-E38) and then bring me back the value in Column B of those sheets for the matched column A item.

I have the formula I tried to adapt below:

=iferror(ifna(IF($B29="Set",INDEX('Set DD Skill by Skill'!$B$3:$B,match($D29,'Set DD Skill by Skill'!$A$3:$A,0)),na()),if($B29="Vol",INDEX('Vol DD Skill by Skill'!$B$3:$B,match($D29,'Vol DD Skill by Skill'!$A$3:$A,0)),na()),if($B29="EXTVol",INDEX('EXTVol DD Skill by Skill'!$B$3:$B,match($D29,'EXTVol DD Skill by Skill'!$A$3:$A,0)),na()),if($B29="EXTVol2",INDEX('EXTVol2 DD Skill by Skill'!$B$3:$B,match($D29,'EXTVol2 DD Skill by Skill'!$A$3:$A,0)),na()),if($A29="DMT",INDEX('DMT DD Skill by Skill'!$B$3:$B,match($D29,'DMT DD Skill by Skill'!$A$3:$A,0)),"")),"")

And also a link to a copied version of the sheet for reference:

https://docs.google.com/spreadsheets/d/1pkN3tKTFhFweDdKfQ3SZDnA_gqqF-B5rso1zc1v3TZY/edit?usp=sharing

Thanks in advance for anyone who is able to help with this !

Elliot

kirkg13 kirkg13 · Accepted Answer · 2020-09-03T19:30:25

I may have an answer that works for you. Try this formula in E29, and drag it down:

=IF($B29="Set",           ifna(INDEX('Set DD Skill by Skill'!$B$3:$B,     match($D29,'Set DD Skill by Skill'!$A$3:$A,0)),    "Skip1"),
   if($B29="Vol",         ifna(INDEX('Vol DD Skill by Skill'!$B$3:$B,     match($D29,'Vol DD Skill by Skill'!$A$3:$A,0)),    "Skip2"),
     if($B29="EXTVol",    ifna(INDEX('EXTVol DD Skill by Skill'!$B$3:$B,  match($D29,'EXTVol DD Skill by Skill'!$A$3:$A,0)), "Skip3"),
       if($B29="EXTVol2", ifna(INDEX('EXTVol2 DD Skill by Skill'!$B$3:$B, match($D29,'EXTVol2 DD Skill by Skill'!$A$3:$A,0)),"Skip4"),
         if($A29="DMT",   ifna(INDEX('DMT DD Skill by Skill'!$B$3:$B,     match($D29,'DMT DD Skill by Skill'!$A$3:$A,0)),    "Skip5"),
           "Skip6")))))

I just shuffled the formula you had there, and after a bit, it seemed to work out. You may want to review the IFERROR or IFNA statements that you want as part of that. You had NA() Where I have "Skip x", which helped me figure out what was happening.

Let me know if this isn't what you were looking for.

Alternate to IFNA for my INDEX MATCH formula

2 Answers