How do I take information from two sheets and auto populate into a third sheet?

Jeremy Oesch

I am trying to set up this smart sheet where it has a entity that someone can select from a drop down menu. Once they clicked any entity I had it V lookup from another sheet and it would auto generate the address for that entity from a different sheet into the cell next to it. I figured out how to do this part.

I am wondering is there a way to do this same function but have it reference two different sheets at the same time. both would have different named entities and different addresses. I don't want to combine then since they are large sheets.

Thanks,

Jeremy

Nick Korna

Hi @Jeremy Oesch,

You can do this with a combination of IFERROR & VLOOKUP or IFERROR and INDEX/MATCH.

For VLOOKUP:

=IFERROR(IFERROR(VLOOKUP(Name@row, {1st Range}, 2, false), VLOOKUP(Name@row, {2nd Range}, 2, false)), "Not found")

In this, the ranges are the name & address columns - if you have more than obviously the number in the VLOOKUP would be larger. The nested IFERRORs mean if the name/address can't be found on the 1st sheet, it tries to find it in the 2nd. If it can't be found the "Not found" message is displayed instead.

For INDEX/MATCH:

=IFERROR(IFERROR(INDEX({1st sheet address column}, MATCH(Name@row, {1st sheet name column}, 0)), INDEX({2nd sheet address column}, MATCH(Name@row, {2nd sheet name column}, 0))), "Not found")

The range names should hopefully be pretty self-explanatory there - the results are the same though:

Example output, where A is in sheet 1 and B in sheet 2:

If you've any questions etc. then just post! 😊