Formula to pull values from another sheet based on another column

SheetHacker

Hi everyone!

I need some help pulling a value from Sheet A Column 2 to Sheet B Column 2 if Sheet B Column 1 is the same as Sheet A Column 1.

In my example, I want the use cases to populate based on the use cases in the master sheet if the collateral ID is the same.

Paul Newcome

Try something like this:

=INDEX({Sheet A Use Case Column}, MATCH([Collateral ID]@row, {Sheet A Collateral ID Column}, 0))

SheetHacker

Thank you!

I tried

=INDEX({All Security Assets - Use Case}, MATCH([Collateral ID]@row, {All Security Assets - Collateral ID}, 0))

And I get

#INVALID REF

It doesn't like linking to the other sheet with the =INDEX formula. I'll play around and see if I can make it work unless you have other ideas.

Paul Newcome

You will need to make sure that you are creating the cross sheet references properly.

Start typing.

=INDEX(

Click on the little blue link in the formula helper box that says "Reference Another Sheet".

Select the source sheet.

Click on the appropriate column header.

Click on "Insert Reference" in the bottom left corner.

You should now see:

=INDEX({Sheet Name Range 1}

with the cross sheet reference having been automatically inserted.

SheetHacker

I am not allowed to choose the sheet using that method with =INDEX (it won't show up in the pop-up where you choose the reference sheet), but I can with a formula like =COUNTIF.

Paul Newcome

It sounds like there may be an issue on the back-end. You may want to reach out to support.

In the meantime, you should be able to create the reference using a COUNTIF and then switch over to the INDEX function.