Forum Discussion
VLOOKUP across sheets
Hello everyone,
I am trying to use VLOOKUP to reference a second sheet (attached is the spreadsheet I am using). I want to ask column C in the 'Activities' sheet (a number to represent the 'Type' of activity) to autopopulate once someone has selected a number from the drop-down list in column E in the 'Activities' sheet. This drop-down list is the same as column H in the 'Key' sheet. The formula I have tried is in cell C5 in the 'Activities' sheet and is: =VLOOKUP($E$5,Key!H3:I34,7,FALSE). The error message I am getting is #REF!.
Any help in resolving this formula would be very much appreciated - thank you!
Georgina
Hi Georgina
The issue was that you were looking up column H:I (2 columns) but wanting to retrieve data from the 7th column - so you were getting the #REF error.
I have changed the range to H:N columns - and then retrieving the data from 7th column - this returns 0 (space). I added a second line and retrieved data from 4th column so you could understand how this work. Hope this helps!
Peter
9 Replies
Hi Georgina
The issue was that you were looking up column H:I (2 columns) but wanting to retrieve data from the 7th column - so you were getting the #REF error.
I have changed the range to H:N columns - and then retrieving the data from 7th column - this returns 0 (space). I added a second line and retrieved data from 4th column so you could understand how this work. Hope this helps!
Peter
peteryac60 Thank you so much, you have solved all my problems! I have amended the original spreadsheet so that you can now select an HEI Descriptor from a drop-down list, and the selection auto-populates three other cells. Thank you!
- Your VLOOKUP formula looks at two columns and then you are asking it to return data from the 7th column. That is impossible.
JKPieterse Thank you - do you know if this is a problem with the formula, or with what I want to do? I want a cell to autopopulate with a number from one column in a different sheet, depending on the value someone selects in a drop-down list in another column.
