Vlookup Multiple Sheets

1
votes

I trying to use a table on Sheet 3 as a reference for Sheet 1. The formula I'm using is =VLOOKUP(N1,Sheet3!A1:B15428,2,FALSE). This works for a single cell,but I cannot copy the formula to other cells in the same column. Every time I try Excel gives me an #N/A error. I do not want to manually add the formula to 100,000 cells. What am I doing wrong? If you need to see the spread sheet just message me. Thank you,

Kim

2
excelvlookup

2 Answers

2
votes

Excel might be referencing cells that are out of range. Freeze the reference cells from Sheet 3 using $ symbol:

=VLOOKUP(N1,Sheet3!$A$1:$B$15428,2,FALSE)
0
votes

You need to use absolute referencing instead of relative reference, same as mentioned in another answer.

You can check the issue by copying the formula to another cell and see the cells being referenced, the range will be shifted if relative reference is being used, which you are using right now.

Also, if you foresee that extra rows might be added in sheet3, then you can use whole columns i.e. A:B as reference and any additional rows will automatically be referenced.

=VLOOKUP(N1,Sheet3!A:B,2,FALSE)