Would like to pull information from another sheet

Diego Casillas Zaragoza

I have a main sheet, when I enter information in my primary column, I would like other columns to be auto populated with matching information from another worksheet. I have no idea how to make that happen.

Paul Newcome

Is the data in the Slot column unique on every row in the worksheet you are wanting to pull from?

Diego Casillas Zaragoza

Hi, Paul,

yes it is

Paul Newcome

Ok. In that case I would suggest an INDEX/MATCH.

=INDEX({Source Sheet Column To Pull From}, MATCH(Slot@row, {Source Sheet Slot Column}, 0))

Amy G

Hello,

I am trying to do this same thing, but reference four different sheets. I've been successful at two, but can't get the third to work. I haven't attempted four since I can't figure out three. Here is what I've done and tried:

Successful single sheet reference:

=IFERROR(INDEX({Sheet A Range 1}, MATCH([ref]@row, {Sheet A Range 2}, 0)), "")

Successful two-sheet reference:

=IFERROR(IFERROR(INDEX({Sheet A Range 1}, MATCH([ref]@row, {Sheet A Range 2}, 0)), INDEX({Sheet B Range 1}, MATCH([ref]@row, {Sheet B Range 2}, 0))), "")

Returns #incorrect argument set:

=IFERROR(IFERROR(INDEX({Sheet A Range 1}, MATCH(ref]@row, {Sheet A Range 2}, 0)), INDEX({Sheet B Range 1}, MATCH([ref]@row, {Sheet B Range 2}, 0)), INDEX({Sheet C Range 1}, MATCH([ref]@row, {Sheet C Range 2}, 0))), "")

Returns #incorrect argument set:

=IFERROR(IFERROR(IFERROR(INDEX({Sheet A Range 1}, MATCH([ref]@row, {Sheet A Range 2}, 0)), INDEX({Sheet B Range 1}, MATCH([ref]@row, {Sheet B Range 2}, 0)), INDEX({Sheet C Range 1}, MATCH([ref]@row, {Sheet C Range 2}, 0)))), "")

I manipulated my formulas based on this one I found on another community thread which has two iferrors and three references:

=iferror(iferror(INDEX({Site Number}, MATCH([Site ID]62, {Site Name}, 0)),INDEX({Site Number ref 2}, MATCH([Site ID ref2]62, {Site Name ref 2}, 0)),INDEX({Site Number ref 3}, MATCH([Site ID ref 3]62, {Site Name ref3}, 0))

I've tried moving things around, but there is something I don't understand in this logic and I can't figure it out.

Thanks!

Amy

Paul Newcome

@Amy G Try working your formula using this line of thinking...

The IFERROR is designed to replace an error. So if there is no match on the first INDEX/MATCH, then you can use the IFERROR to run the second INDEX/MATCH

=INDEX({First Sheet}, MATCH(.............))

=IFERROR(INDEX({First Sheet}, MATCH(.............)), run 2nd sheet formula)

=IFERROR(INDEX({First Sheet}, MATCH(.............)), INDEX({Second Sheet}, MATCH(.............)))

So now we have

=IFERROR(1st sheet formula, 2nd sheet formula)

Now we can say that if there is an error with that one (meaning not located in either of the two sheets, then we want to run the 3rd sheet formula. So we nest the entire first IFERROR into the first portion of the IFERROR statement, then put the 3rd sheet formula in the second portion.

=IFERROR(original formula, 3rd sheet formula)

=IFERROR(IFERROR(INDEX({First Sheet}, MATCH(.............)), INDEX({Second Sheet}, MATCH(.............))), 3rd sheet formula)

Now we do the same exact thing for the 4th sheet formula

=IFERROR(first 3 sheets formula, 4th sheet formula)

=IFERROR(IFERROR(IFERROR(INDEX({First Sheet}, MATCH(.............)), INDEX({Second Sheet}, MATCH(.............))), INDEX({Third Sheet}, MATCH(.............))), 4th sheet formula)

=IFERROR(IFERROR(IFERROR(INDEX({First Sheet}, MATCH(.............)), INDEX({Second Sheet}, MATCH(.............))), INDEX({Third Sheet}, MATCH(.............))), INDEX({Fourth Sheet}, MATCH(.............)))

And you can keep following that to add even more if needed. You would add the IFERROR as the very first function after the initial =. Then you would go to the very end of the existing formula, add a comma, then enter your output for if there is an error and close it out with one more closing parenthesis.

Amy G

@Paul Newcome

Thank you for the prompt response! I didn't realize it was here. I have updated my notification settings to email me when mentioned.

This logic makes sense to me. You explained it very well and I was able to implement it in my sheet.

I tested my understanding by nesting an additional IFERROR to leave the cell blank if no match in any of the sheet references.

Thank you!!

Amy

Paul Newcome

Happy to help. 👍️