Display the results of one cell based on another

Hi Excel Forum!

I am working with a formula that works perfectly! Basically, the formula:

=IF(ISERROR(FIND("TX", AllTracking!AA16)) = FALSE, AllTracking!AB16, "")

displays the results of AB16 if AA16 match "TX". That works great but now I need to add some layers. I need to add not only "TX", but also "HV", "CSSRS", "GAD" and "SRA". How can I modify my formula to include searching for these items. Using OR maybe??

Additionally, the cells to look for is actually AA16:AJ16.

I have also tried =IF(AA17="HV",DA17,IF(AA17="TX",DE17, IF(AA17="SRA",DL17,IF(AA17="GAD",DS17,IF(AA17="PHQ",DO17, IF(AA17="CSSRS",DW17,"")))))) but to no avail....

Any help will be appreciated!

Thanks!

=IF(SUM(COUNTIFS(AllTracking!AA16:AJ16,{"TX","HV","CSSRS","GAD","SRA"})),"Yes","No")

Will return Yes if any of those are found in that range, otherwise No.

If you include a sample workbook where we can see the desired results, we can modify the output.

If you want to return something different depending on what value is found, you should use a lookup table to do that.

Basically have one column with the lookup value and one column with the result value, then use a VLOOKUP or INDEX MATCH formula.

Capture.JPG

Here is a sample of my data. Basically, each ID is a person. The date is when each code was discussed. I want to display the date the code was discussed with each person in its corresponding column. So 1234 had a code of HV on 10-15-19. In the corresponding column, it shows 10-15-19. I want the date to be entered in the column automatically. So when I type HV and the date, it is returned in the HV Column.

hope this helps!

Thanks

You can use this in H3:

=IFERROR(INDEX($C3:$F3,MATCH(H$2,$B3:$E3,0)),"")
Drag to the right and down.

In the future, please post a sample workbook as requested (not a picture).
See yellow banner towards the top of the page.

This is perfect!!!!

Thanks so much!!!!

You're welcome. Happy to help.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.