confused about VLOOKUP across sheets

joechap

I can't figure out how to make the function work or I'm trying to do something not supported.

Basically, I want to pull the value of a cell from a row on sheet B and put it in a cell on the same row in sheet A. I'm not looking to perform any specific calculations on the data, just to grab a copy.

What I'm trying to do is work around not being able to limit access to certain columns in a given sheet for specific people. My work around is trying to use VLOOKUP to pull values from corresponding rows in one sheet and have them appear in another sheet. My Sheet B has a couple of dozen columns and I want copies of 6 of those columns to appear in Sheet A and then share sheet A (or a report of) to people. I'd also be using some Alert & Actions Notifications on sheet A.

Is there a way to reference a row of a range of the source based on the row of the destination?

J. Craig Williams

Yes.

Depending on your data. You will need to set up a unique identifier (a 'key').

I'll post some examples on my website soon.

Craig

joechap

So, I figured out the basics and I did get it working.

In Row 1, Column 2, of Sheet_A, I put in

=VLOOKUP([ID]1, {Sheet_B Range 1}, 2, false)

[ID]1 of Sheet_A is the same value as found in Sheet_B, Col 1.

I had to use "false" to get an exact match.

I repeated for additional columns and it works.

The problem is I have to copy/paste all the values of Sheet_B, Col 1 in to Sheet_A, Col 1 and this will be problematic over time as I continue to add new rows to Sheet_B.

Is there a way to just reference the Row# in Sheet_B from in the formula in Sheet_A?

 

*update*

Ok, so I cobbled together a workaround by adding an 'Index' column to each sheet using autogen numbers so that rows 1...200 have a value of 1..200 in the 'Index' column. I can use that to pull my 'real' unique ID that I track things by from Sheet_B.

I also have Sheet_C (another source) that uses the same unique ID as in Sheet_B (the original source). So, I can get the ID from Sheet_B, match that in Sheet_C and pull additional data from Sheet_C.

So, it looks like as long as I have unique values, it works fine. Unfortunately it's not always unique. Sometimes there are child rows with repeat values under a parent ID. That creates offsets that screw everything up.

There's also a problem where anyone looking at Sheet_A still needs view permission on Sheets_B & C. I was hoping to use this as a workaround so I could hide the source sheets because there's data in there I don't want people to see.

I guess I could hide and lock columns in the source sheets and create reports that reference the columns in Sheet A, B or C for those that need them. The whole reason we're using sheets instead of reports to try to do this is that Sheets support notifications/alerts and Reports do not.

J. Craig Williams

The range on Sheet B can be the whole column.

Did you try that?

The users shared to Sheet A do not need permissions to see Sheet B. The reference is created by someone (you) that can see both. 

Craig

joechap

I was going by the description of VLOOKUP and I just tested it out and discovered that (YEAH!) Sheet A users don't need to be shared Sheet B (or C) which is just perfect! Saves me a step.

Now, Sheet A users can have their own alerts/notifications as well.

As for the range including a whole column, yes, however, you're still limited to 25K cells.

If the # of whole columns X # of rows exceeds that, It rejects the reference — found out first hand.

Had to move some columns around to deal with that. Now I need to start getting people use to using a report to work with the data in the sheet instead of hitting the sheet directly.