SOLVED

VLOOKUP with two variable columns

%3CLINGO-SUB%20id%3D%22lingo-sub-2836874%22%20slang%3D%22en-US%22%3EVLOOKUP%20with%20two%20variable%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2836874%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20two%20drop%20down%20list%20cells%20in%20two%20different%20columns%20and%20I%20want%20the%20third%20column%20to%20display%20the%20correct%20value%20based%20on%20which%20two%20variables%20are%20selected.%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20first%20column%20has%203no.%20variables%20(text)%20(glazing%2Fshading%20type)%20and%20the%20second%20column%20has%204no.%20variables%20(north%2C%20south%20east%20and%20west)%20and%20I%20wish%20the%20third%20column%20to%20%3D%20a%20value%20based%20on%20the%20selected%20combination%20(number).%20See%20images%20attached.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMany%20thanks%20in%20advance.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EJamie%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2836874%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-2837461%22%20slang%3D%22en-US%22%3ERe%3A%20VLOOKUP%20with%20two%20variable%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2837461%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1182861%22%20target%3D%22_blank%22%3E%40jamie1978%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ELet's%20say%20your%20lookup%20table%20is%20in%20range%20B2%3AD13.%20Dropdowns%20are%20in%20F2%20and%20G2.%20Then%20you%20can%20apply%20formula%3A%3C%2FP%3E%3CP%3E%3DVLOOKUP(F2%26amp%3BG2%2CCHOOSE(%7B1.2%7D%2CB2%3AB13%26amp%3BC2%3AC13%2CD2%3AD13)%2C2%2CFALSE)%3C%2FP%3E%3CP%3EFormula%20has%20to%20be%20entered%20as%20matrix%20with%20ctrl%2Bshift%2Benter.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2837739%22%20slang%3D%22en-US%22%3ERe%3A%20VLOOKUP%20with%20two%20variable%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2837739%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1182861%22%20target%3D%22_blank%22%3E%40jamie1978%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20noticed%20that%20in%20your%20Excel%20sheet%26nbsp%3B%26nbsp%3B%7B1%3CFONT%20color%3D%22%23FF0000%22%3E.%3C%2FFONT%3E2%7D%26nbsp%3B%20was%20entered%20as%26nbsp%3B%20%26nbsp%3B%7B1%3CFONT%20color%3D%22%23FF0000%22%3E%2C%3C%2FFONT%3E2%7D.%20I%20work%20with%20german%20version%20of%20Excel%20and%20i%20have%20to%20enter%26nbsp%3B%20%26nbsp%3B%7B1.2%7D%20in%20order%20to%20make%20the%20formula%20work.%20Try%20to%20enter%26nbsp%3B%7B1%3CFONT%20color%3D%22%23FF0000%22%3E.%3C%2FFONT%3E2%7D%26nbsp%3B%20%26nbsp%3Bas%20well.%20I%20hope%20it%20works.%20Otherwise%20you%20would%20have%20to%20check%20how%20data%20in%20curly%20brackets%20has%20to%20be%20entered%20in%20your%20version%20of%20excel.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20didn't%20understand%20that%20you%20want%20to%20copy%20formula%20down%20from%20cell%20U9%20to%20U12.%3C%2FP%3E%3CP%3E%3DVLOOKUP(S9%26amp%3BT9%2CCHOOSE(%7B1.2%7D%2C%24AE%2411%3A%24AE%2422%26amp%3B%24AF%2411%3A%24AF%2422%2C%24AG%2411%3A%24AG%2422)%2C2%2CFALSE)%3C%2FP%3E%3CP%3EFormula%20has%20to%20be%20adjusted%20and%20entered%20as%20matrix%20with%20ctrl%2Bshift%2Benter%20and%20then%20it%20can%20be%20copied%20down.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hi,

 

I have two drop down list cells in two different columns and I want the third column to display the correct value based on which two variables are selected. 

The first column has 3no. variables (text) (glazing/shading type) and the second column has 4no. variables (north, south east and west) and I wish the third column to = a value based on the selected combination (number). See images attached.  

 

Many thanks in advance. 

 

Jamie 

4 Replies

@jamie1978 

Let's say your lookup table is in range B2:D13. Dropdowns are in F2 and G2. Then you can apply formula:

=VLOOKUP(F2&G2,CHOOSE({1.2},B2:B13&C2:C13,D2:D13),2,FALSE)

Formula has to be entered as matrix with ctrl+shift+enter.

@Quadruple_Pawn 

Many thanks for your reply, however when I do this it just states #ref!

My lookup table is outside of my main spreadsheet in cells ae11:ae22 (glazing type column), AF11:AF22 (orientation) and AG11:AG22 (solar gain watts).

My two drop downs are in S9 and T9.

There are two things I'm not sure of in spreadsheet, one is the orientation drop down list and second is how i have compiled the lookup list. 

I have attached the spreadsheet for info. Could you please check it is correct? 

Many thanks 

 

Jamie 

best response confirmed by jamie1978 (New Contributor)
Solution

@jamie1978 

I noticed that in your Excel sheet  {1.2}  was entered as   {1,2}. I work with german version of Excel and i have to enter   {1.2} in order to make the formula work. Try to enter {1.2}   as well. I hope it works. Otherwise you would have to check how data in curly brackets has to be entered in your version of excel.

 

I didn't understand that you want to copy formula down from cell U9 to U12.

=VLOOKUP(S9&T9,CHOOSE({1.2},$AE$11:$AE$22&$AF$11:$AF$22,$AG$11:$AG$22),2,FALSE)

Formula has to be adjusted and entered as matrix with ctrl+shift+enter and then it can be copied down.

Many thanks for your help much appreciated. I input 1,2 and it worked!

Thank you