Forum Discussion

H_Stuart's avatar
H_Stuart
Copper Contributor
Nov 23, 2021
Solved

HLOOKUP Into VLOOKUP in two separate sheets.

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.

 

 

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

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.

    • H_Stuart's avatar
      H_Stuart
      Copper Contributor

      OliverScheurich 

       

      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. 

       

       

      • OliverScheurich's avatar
        OliverScheurich
        Gold Contributor

        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.

Resources