I am using VLOOKUP to pull data from one sheet another. I am getting #INVALID REF

Jerry Corpening

THIS IS THE SOURCE TABLE

THIS IS THE TABLE THAT IS BEING UPDATED WITH THE VLOOKUP

This is my formula

=VLOOKUP([Materials Description]@row, {TDO Material Cost Tracking Sheet All Values}, 3, 0)

Paul Newcome

That means the formula does not recognize the cross sheet reference. How exactly did you create it?

Paul Newcome

You will want to follow the steps for creating a valid cross sheet reference.

Start typing your formula:

=VLOOKUP([Materials Description]@row, {TDO Material Cost Tracking Sheet All Values}, 3, 0)

In the small helper box there should be a blue link to "Reference another sheet". Click on that, select the source sheet from the left, then click on the column header that contains the data you are matching on. From there you want to shift+click on the column header that contains the data you want to pull in. Finally you will click on the blue box in the bottom right corner that says "Insert Reference".

Paul Newcome

That means the formula does not recognize the cross sheet reference. How exactly did you create it?

Jerry Corpening

I created it as a new sheet and inputted the data manually.

Paul Newcome

You will want to follow the steps for creating a valid cross sheet reference.

Start typing your formula:

=VLOOKUP([Materials Description]@row, {TDO Material Cost Tracking Sheet All Values}, 3, 0)

In the small helper box there should be a blue link to "Reference another sheet". Click on that, select the source sheet from the left, then click on the column header that contains the data you are matching on. From there you want to shift+click on the column header that contains the data you want to pull in. Finally you will click on the blue box in the bottom right corner that says "Insert Reference".

Jerry Corpening

In my formula I created the reference using

Jerry Corpening

Thanks Paul...This seems to work, but I don't understand why I can not reference the entire table and identify columns in the formula.

Paul Newcome

You would need to select all of the column headers with the match column being on the far left of the table. The third portion of the VLOOKUP is where you enter which column number you want to pull from.

My personal preference though is an INDEX/MATCH. It provides more flexibility and does not require you to select an entire table.

=INDEX({Column To Pull From}, MATCH([Materials Description]@row, {Column To Match On}, 0))

Jerry Corpening

Thanks, I will try that.