How to pull information from a cell on another sheet
I am trying to search multiple other sheets for information to pull back one cell of information. The formula I was attempting to use was
=IFERROR(INDEX(COLLECT({Quote Log 2024 LCI SO}, {Quote Log 2023 LCI SO}, {Quote Log 2022 LCI SO}, {Quote Log 2021 LCI SO}, {Quote Log 2020 LCI SO}), ({Quote Log 2020 LCI Quote #}, {Quote Log 2021 LCI Quote #}, {Quote Log 2022 LCI Quote #}, {Quote Log 2023 LCI Quote #}{Quote Log 2024 LCI Quote #}), @cell = [Quote Number]@row), 1), "")
with no success. The error is #UNPARSEABLE. The column type is a text column.
Neither of the columns that I am wanting the formula to look for both matching and pulling the information over from are the primary columns on their respective sheets.
Any assistance is greatly appreciated.
Answers

Hi @Mallory N
Are you trying to populate one cell with multiple values from different sheets or just one value from any one of multiple sheets? i.e., if the value exists in any one sheet, you want it to be pulled.
Thanks,
Aravind GP Principal Consultant
Atturra Data & Integration
M: +61493337445
E:Aravind.GP@atturra.com
W: www.atturra.com

One value from any one of multiple sheets.

Hi @Mallory N
You'll need to have only one sheet referenced within a Function. You can search multiple sheets, but each sheet will need to be its own INDEX(MATCH formula combination in order to search that sheet full, close off the function, then look in another sheet, does that make sense?
For example:
=IFERROR(INDEX(first sheet), INDEX(second sheet))
With multiple sheets, you can keep adding IFERRORs:
=IFERROR(IFERROR(IFERROR(INDEX(first sheet), INDEX(second sheet)), INDEX(third sheet)), INDEX(fourth sheet))
So in your case, this would be your first sheet searchthrough (assuming you want to start with 2024 as your primary sheet to check and work backwards):
=INDEX({Quote Log 2024 LCI SO}, MATCH([Quote Number]@row, {Quote Log 2024 LCI Quote #}, 0))
Then if that has no match, you'll check the 2023 sheet:
=IFERROR(INDEX({Quote Log 2024 LCI SO}, MATCH([Quote Number]@row, {Quote Log 2024 LCI Quote #}, 0))), INDEX({Quote Log 2023 LCI SO}, MATCH([Quote Number]@row, {Quote Log 2023 LCI Quote #}, 0))
So your full formula may look something like this:
=IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(INDEX({Quote Log 2024 LCI SO}, MATCH([Quote Number]@row, {Quote Log 2024 LCI Quote #}, 0))), INDEX({Quote Log 2023 LCI SO}, MATCH([Quote Number]@row, {Quote Log 2023 LCI Quote #}, 0))), INDEX({Quote Log 2022 LCI SO}, MATCH([Quote Number]@row, {Quote Log 2022 LCI Quote #}, 0))), INDEX({Quote Log 2021 LCI SO}, MATCH([Quote Number]@row, {Quote Log 2021 LCI Quote #}, 0))), INDEX({Quote Log 2020 LCI SO}, MATCH([Quote Number]@row, {Quote Log 2020 LCI Quote #}, 0))), "")
Let us know if this makes sense and works for you!
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8  10, Seattle, WA  Register now
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 63K Get Help
 379 Global Discussions
 212 Industry Talk
 442 Announcements
 4.6K Ideas & Feature Requests
 140 Brandfolder
 129 Just for fun
 130 Community Job Board
 449 Show & Tell
 30 Member Spotlight
 1 SmartStories
 305 Events
 34 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!