How to pull information from a cell on another sheet

Mallory N

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.

AravindGP

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.

Mallory N

One value from any one of multiple sheets.

Genevieve P.

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 search-through (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