Forum Discussion
VLOOKUP with two variable columns
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 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
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