Vlookup and Nested IF condition checker
HI All,
I have a table which has denial codes and whether it is part of a group which is available in sheet - "Codes"
I have another table (sheet-'Data") where for a transaction multiple denial codes arise, and if any is part of the group in the previous table, then the output should be Yes
We are currently, delimiting the denial codes, using vlookup and manually checking whether yes or no arises. Is there anyway to do it dynamically using formula.
Last edited by Bhurockgun; 07-16-2019 at 06:13 AM.
Not entirely sure I follow the requirement - but if you're saying you should have Yes returned in Column C where 1+ code in Col B can be found in first sheet with Yes assigned then...
Assuming latest XL version
D1:=","&TEXTJOIN(",",TRUE,INDEX(REPT(SUBSTITUTE(Codes!A2:A9," ",""),Codes!B2:B9="Yes"),0))&","
C2:=REPT("Yes",ISNUMBER(LOOKUP(9.99E+307,1/COUNTIF(D$1,"*,"&SUBSTITUTE(TRIM(MID(SUBSTITUTE($B2,",",REPT(" ",100)),1+100*(ROW($1:$10)-1),100))," ","")&",*"))))
copied down to C6
above would return Yes in C2, C4, C5 & C6
See attached file, I used this code:
Regards,
Please Login or Register to view this content.
Antonio
It is working, but very difficult to understand the formula In the C2 cell. Thanks a lot.
Brilliant Thanks a lot. This would be helpful if it is combined with =IF(ISNUMBER(SEARCH("Yes",C2)),"Yes","No").
Excuse me, I didn't understand fine.
Please try new attached file, I hope that it's what you need.
I used this code:
Regards,Please Login or Register to view this content.
Antonio
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks