Look for matching fields in multiple Smartsheet's and return an adjacent field.

CNC

I have an ID number that can be located in one of multiple Smartsheet's. I would like to look for and match this ID number and return a field from the same row from whichever Smartsheet (SS)matches the ID number.

I have multiple formulas that allow me to find the matching ID# from only one SS:

1) =VLOOKUP([AIE-ID#]@row, {No Action[s] Range 2}, 12, false)

2) =INDEX({No Action[s] Range 3}, MATCH([AIE-ID#]@row, {No Action[s] Range 1}, 0))

But I need to look in other SS's to find the matching ID number as well. I have not been able to get a nested IF statement to work. I've tried:

1) =IF([AIE-ID#]@row = {Action[s] Plan Range 1}, VLOOKUP([AIE-ID#]@row, {Action[s] Plan Range 2}, 22, true), IF([AIE-ID#]@row = {No Action[s] Range 1}, VLOOKUP([AIE-ID#]@row, {No Action[s] Range 2}, 12, true), ""))

The formula will be located in the IE Course Evaluation ImprovementComments column. The formula should be looking in the Action[s] Plan SS and then looking in the No Action[s] SS to match the ID number and pull return whatever is in the ImprovementComments column from those respective SS's.

Here are pictures:

Any help would be sincerely appreciated. THANK YOU IN ADVANCE.

Nick Korna

There may be some bracket count errors here, but hopefully this at least will get you pointed in the right direction...

=IFERROR(INDEX({No Action[s] Range 3}, MATCH([AIE-ID#]@row, {No Action[s] Range 1}, 0)),IFERROR(INDEX({Action[s] Plan Range 2}, MATCH([AIE-ID#]@row, {Action[s] Plan Range 1}, 0)), INDEX({Next FY Consideration Action[s] Range 1}, MATCH([AIE-ID#]@row, {Next FY Consideration Action[s] Range 2}, 0)),"Not found")

If IFERRORS are basically saying "If you can't find the result in this sheet, move to the next one" - the "Not found" at the end can be replaced with something else, pick whatever you would want to show if the reference can't be found on any of the sheet.

I would not be too shocked if you get an error (apologies if so!), but you can likely amend the working formula you have now to check if it's down to the 2nd IFERROR or is unrelated.

Nick Korna

You should be able to do this with a nested IFERROR. Have the IFERROR(<insert your choice of formula here for the first sheet>), then for the "Value not found" condition another IFERROR pointing at the 2nd sheet, then similar for the 3rd (and a blank/"not found" type result for the final "Value not found").

CNC

@Nick Korna - Thank you! I actually do have a third SS that I will also need to match the ID number and potentially return a row field.

So, using your idea worked with TWO of the SS's. But I can't get the formula to work with the THIRD. Do you see where I am doing it incorrectly?

Worked: =IFERROR(INDEX({No Action[s] Range 3}, MATCH([AIE-ID#]@row, {No Action[s] Range 1}, 0)), INDEX({Action[s] Plan Range 2}, MATCH([AIE-ID#]@row, {Action[s] Plan Range 1}, 0)))

Doesn't work: =IFERROR(INDEX({No Action[s] Range 3}, MATCH([AIE-ID#]@row, {No Action[s]Range 1}, 0)), INDEX({Action[s] Plan Range 2}, MATCH([AIE-ID#]@row, {Action[s] Plan Range 1}, 0)), INDEX({Next FY Consideration Action[s] Range 1}, MATCH([AIE-ID#]@row, {Next FY Consideration Action[s] Range 2}, 0)))

Nick Korna

There may be some bracket count errors here, but hopefully this at least will get you pointed in the right direction...

=IFERROR(INDEX({No Action[s] Range 3}, MATCH([AIE-ID#]@row, {No Action[s] Range 1}, 0)),IFERROR(INDEX({Action[s] Plan Range 2}, MATCH([AIE-ID#]@row, {Action[s] Plan Range 1}, 0)), INDEX({Next FY Consideration Action[s] Range 1}, MATCH([AIE-ID#]@row, {Next FY Consideration Action[s] Range 2}, 0)),"Not found")

If IFERRORS are basically saying "If you can't find the result in this sheet, move to the next one" - the "Not found" at the end can be replaced with something else, pick whatever you would want to show if the reference can't be found on any of the sheet.

I would not be too shocked if you get an error (apologies if so!), but you can likely amend the working formula you have now to check if it's down to the 2nd IFERROR or is unrelated.

CNC

@Nick Korna - This is great! The 3rd IFERROR was missing. With that, the formula works great. THANK YOU SOOOO MUCHHHH!!

WORKED:

=IFERROR(INDEX({No Action[s] Range 3}, MATCH([AIE-ID#]@row, {No Action[s] Range 1}, 0)), IFERROR(INDEX({Action[s] Plan Range 2}, MATCH([AIE-ID#]@row, {Action[s] Plan Range 1}, 0)), IFERROR(INDEX({Next FY Consideration Action[s] Range 1}, MATCH([AIE-ID#]@row, {Next FY Consideration Action[s] Range 2}, 0)), "Not found")))