SOLVED

VLOOKUP across sheets

Copper Contributor

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  

9 Replies
Your VLOOKUP formula looks at two columns and then you are asking it to return data from the 7th column. That is impossible.

@Jan Karel Pieterse 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.

best response confirmed by georgina450 (Copper Contributor)
Solution

@georgina450 

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!

@georgina450 

 

You are welcome!

 

Can you please mark this solution as complete!

 

thank you.

I'm so sorry - I can't find or see how to do this, even with the help of Google!

@georgina450 

 

Hi Georgina,

 

Sorry - what is the issue you are having?

 

thanks,

 

Peter

I can't find a way to mark this conversation as complete.

@georgina450 

 

You already did - marked it as best solution!

 

all the best!

 

Peter

1 best response

Accepted Solutions
best response confirmed by georgina450 (Copper Contributor)
Solution

@georgina450 

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

View solution in original post