SOLVED

HLOOKUP Into VLOOKUP in two separate sheets.

%3CLINGO-SUB%20id%3D%22lingo-sub-2998690%22%20slang%3D%22en-US%22%3EHLOOKUP%20Into%20VLOOKUP%20in%20two%20separate%20sheets.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2998690%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20All%2C%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20working%20on%20a%20sheet%20that%20calculates%20different%20prices%20for%20our%20products%20according%20to%20where%20it%20gets%20delivered.%20It%C2%B4s%20a%20lot%20of%20data%20to%20load%20manually%2C%20so%20I%C2%B4m%20trying%20to%20make%20it%20read%20from%20one%20sheet%20to%20the%20other%20with%20formulas.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESheet%201%3A%20Is%20the%20list%20where%20I%20have%20the%20product%20code%20%2B%20the%20area%20where%20it%20should%20go.%20(See%20image%201).%26nbsp%3B%3C%2FP%3E%3CP%3ESheet%202%3A%20Includes%20each%20of%20the%20products%20and%20the%20price%20according%20to%20what%20area%20it%20gets%20delivered.%20(See%20Image%202)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20formula%20I%20tried%2C%20which%20didn%C2%B4t%20work%2C%20was%20the%20following%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DVLOOKUP(A25%2C'Prices%20per%20Zone'!B%3AAQ%2CHLOOKUP(H25%2C'Prices%20per%20Zone'!1%3A46%2C1%2C0)%2C0)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20there%20anything%20I%20might%20be%20missing%3F%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMany%20thanks!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EStuart.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2998690%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2998748%22%20slang%3D%22en-US%22%3ERe%3A%20HLOOKUP%20Into%20VLOOKUP%20in%20two%20separate%20sheets.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2998748%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1227794%22%20target%3D%22_blank%22%3E%40H_Stuart%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DVLOOKUP(A25%2C'Prices%20per%20Zone'!B%3AAQ%2C%20MATCH(H25%2C'Prices%20per%20Zone'!B%3AAQ%2C0)%2C0)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMaybe%20this%20works.%20With%20match%20function%20you%20can%20check%20in%20which%20column%20the%20area%20were%20it%20should%20go%20is%20entered.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2998972%22%20slang%3D%22en-US%22%3ERe%3A%20HLOOKUP%20Into%20VLOOKUP%20in%20two%20separate%20sheets.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2998972%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1227794%22%20target%3D%22_blank%22%3E%40H_Stuart%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DVLOOKUP(A25%2C'Prices%20per%20Zone'!%24C%242%3A%24AQ%2436%2C%20MATCH(H25%2C'Prices%20per%20Zone'!%24C%241%3A%24AQ%241%2C0)%2C0)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMaybe%20this%20works.%20With%20match%20function%20you%20can%20check%20in%20which%20column%20the%20area%20were%20it%20should%20go%20is%20entered.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3002351%22%20slang%3D%22en-US%22%3ERe%3A%20HLOOKUP%20Into%20VLOOKUP%20in%20two%20separate%20sheets.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3002351%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1174419%22%20target%3D%22_blank%22%3E%40Quadruple_Pawn%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20replying!%20Unfortunately%2C%20that%20didn%C2%B4t%20seem%20to%20work.%20But%20I%20tried%20using%20an%20IF%20formula%2C%20and%20I%20got%20somewhere%20with%20it.%20Still%20some%20minor%20errors%2C%20but%20they%20are%20more%20workable.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAs%20the%20IF%20formula%20was%20so%20long%2C%20I%20had%20to%20create%202%20CONCAT%20formulas%20to%20narrow%20the%20size%20down.%20The%20used%20the%20VLOOKUP%20formula%20to%20read%20in%20a%20normal%20way%20and%20link%20into%20the%20IF%20formula.%20Not%20sure%20if%20this%20makes%20sense%2C%20its%20quite%20a%20process%2C%20but%20it%20should%20make%20things%20easier%20in%20future.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3002494%22%20slang%3D%22en-US%22%3ERe%3A%20HLOOKUP%20Into%20VLOOKUP%20in%20two%20separate%20sheets.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3002494%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1227794%22%20target%3D%22_blank%22%3E%40H_Stuart%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DVLOOKUP(A2%2C'Prices%20per%20Zone'!%24%3CFONT%20color%3D%22%23FF0000%22%3EB%3C%2FFONT%3E%242%3A%24O%2427%2CMATCH(H2%2C'Prices%20per%20Zone'!%24%3CFONT%20color%3D%22%23FF0000%22%3EB%3C%2FFONT%3E%241%3A%24O%241%2C0)%2CFALSE)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20entered%202%20sample%20sheets%20and%20set%20up%20the%20formula%20according%20to%20your%20data.%20Above%20formula%20should%20work.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20note%20that%20this%20formula%20looks%20similar%20to%20the%20one%20i%20suggested%20yesterday%20but%20there%20is%20an%20important%20difference.%20The%20lookup%20value%20will%20be%20found%20in%20column%20B%20and%20then%20formula%20returns%20the%20expected%20result.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hello All, 

 

I'm working on a sheet that calculates different prices for our products according to where it gets delivered. It´s a lot of data to load manually, so I´m trying to make it read from one sheet to the other with formulas. 

 

Sheet 1: Is the list where I have the product code + the area where it should go. (See image 1). 

Sheet 2: Includes each of the products and the price according to what area it gets delivered. (See Image 2)

 

The formula I tried, which didn´t work, was the following: 

=VLOOKUP(A25,'Prices per Zone'!B:AQ,HLOOKUP(H25,'Prices per Zone'!1:46,1,0),0)

 

Is there anything I might be missing? 

 

Many thanks!

 

Stuart.

 

 

4 Replies

@H_Stuart 

=VLOOKUP(A25,'Prices per Zone'!$C$2:$AQ$36, MATCH(H25,'Prices per Zone'!$C$1:$AQ$1,0),0)

 

Maybe this works. With match function you can check in which column the area were it should go is entered.

@Quadruple_Pawn 

 

Thanks for replying! Unfortunately, that didn´t seem to work. But I tried using an IF formula, and I got somewhere with it. Still some minor errors, but they are more workable. 

 

As the IF formula was so long, I had to create 2 CONCAT formulas to narrow the size down. The used the VLOOKUP formula to read in a normal way and link into the IF formula. Not sure if this makes sense, its quite a process, but it should make things easier in future. 

 

 

best response confirmed by H_Stuart (New Contributor)
Solution

@H_Stuart 

=VLOOKUP(A2,'Prices per Zone'!$B$2:$O$27,MATCH(H2,'Prices per Zone'!$B$1:$O$1,0),FALSE)

 

I entered 2 sample sheets and set up the formula according to your data. Above formula should work.

 

Please note that this formula looks similar to the one i suggested yesterday but there is an important difference. The lookup value will be found in column B and then formula returns the expected result.

Thank you for your help!