SOLVED

VLOOKUP across sheets

%3CLINGO-SUB%20id%3D%22lingo-sub-1463492%22%20slang%3D%22en-US%22%3EVLOOKUP%20across%20sheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1463492%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20everyone%2C%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20use%20VLOOKUP%20to%20reference%20a%20second%20sheet%20(attached%20is%20the%20spreadsheet%20I%20am%20using).%20I%20want%20to%20ask%20column%20C%20in%20the%20'Activities'%20sheet%20(a%20number%20to%20represent%20the%20'Type'%20of%20activity)%20to%20autopopulate%20once%20someone%20has%20selected%20a%20number%20from%20the%20drop-down%20list%20in%20column%20E%20in%20the%20'Activities'%20sheet.%20This%20drop-down%20list%20is%20the%20same%20as%20column%20H%20in%20the%20'Key'%20sheet.%20The%20formula%20I%20have%20tried%20is%20in%20cell%20C5%20in%20the%20'Activities'%20sheet%20and%20is%3A%26nbsp%3B%3DVLOOKUP(%24E%245%2CKey!H3%3AI34%2C7%2CFALSE).%20The%20error%20message%20I%20am%20getting%20is%20%23REF!.%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20help%20in%20resolving%20this%20formula%20would%20be%20very%20much%20appreciated%20-%20thank%20you!%3CBR%20%2F%3EGeorgina%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1463492%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1463555%22%20slang%3D%22en-US%22%3ERe%3A%20VLOOKUP%20across%20sheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1463555%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F22322%22%20target%3D%22_blank%22%3E%40Jan%20Karel%20Pieterse%3C%2FA%3E%26nbsp%3BThank%20you%20-%20do%20you%20know%20if%20this%20is%20a%20problem%20with%20the%20formula%2C%20or%20with%20what%20I%20want%20to%20do%3F%20I%20want%20a%20cell%20to%20autopopulate%20with%20a%20number%20from%20one%20column%20in%20a%20different%20sheet%2C%20depending%20on%20the%20value%20someone%20selects%20in%20a%20drop-down%20list%20in%20another%20column.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1463532%22%20slang%3D%22en-US%22%3ERe%3A%20VLOOKUP%20across%20sheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1463532%22%20slang%3D%22en-US%22%3EYour%20VLOOKUP%20formula%20looks%20at%20two%20columns%20and%20then%20you%20are%20asking%20it%20to%20return%20data%20from%20the%207th%20column.%20That%20is%20impossible.%3C%2FLINGO-BODY%3E
Highlighted
Occasional 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
Highlighted
Your VLOOKUP formula looks at two columns and then you are asking it to return data from the 7th column. That is impossible.
Highlighted

@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.

Highlighted
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

Highlighted

@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!

Highlighted

@georgina450 

 

You are welcome!

 

Can you please mark this solution as complete!

 

thank you.

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

@georgina450 

 

Hi Georgina,

 

Sorry - what is the issue you are having?

 

thanks,

 

Peter

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

@georgina450 

 

You already did - marked it as best solution!

 

all the best!

 

Peter