asked    Jonathan     2018-07-30       excel       168 view        1 Answer

[SOLVED] Excel Hlookup Function

Use the HLOOKUP function in cell H9 to calculate the product pricing based on the color and quantity of the billboards ordered. Product pricing can be referenced on the "Product Pricing" lookup table (range O9:Q18). Be sure to use appropriate relative and absolute cell references

Here is the Excel HyperLink, click over the tabs to "lookup functions", you will find where i'm struggling under cell h9 price.

What I have Which is wrong:

=HLOOKUP(D9,$O$9:$Q$18,2,TRUE)

D9 lookup value refers to #shipped

I have no problem with vlookup but i'm struggling to put in the lookup value and row index number in the right spot for hlookup.

  1 Answer  

        answered    Ivan     2018-07-30      

OK, here is the formula you are looking to put into H9.

=HLOOKUP(C9, $P$9:$Q$18, MATCH(D9, $O$9:$O$18), FALSE)

The HLOOKUP function provides the column (P ro Q) to retrieve the data from by looking for an exact match of the value in C9 against P9:Q9 (the first row in $P$9:$Q$18).

The MATCH function provides which row to retrieve by looking up C9 in O9:O18. This is an approximate match so the values in O9:O18 need to be sorted in ascending order which they are. You want an approximate match because if you have more than 10 (the max in the table), you want it to return the value for 10 and not an error.

So with the row and the column, you are returning the correct value from O9:Q18. Fill down as necessary.

You do have a problem with # Shipped being 1. Either you can cover for quantities greater than 10 or less than 2 but not both. Probably better just to add an entry for a quantity of 1.

    HLOOKUP with MATCH

You will want to familiarize yourself with the IFERROR function to provide error control.

With your lookup table in O9:O18, with the # Shipped starting at 2, you could also shortcut this to,

=HLOOKUP(C9, $P$9:$Q$18, D9, FALSE)

But that not does cover quantities greater than 10 or a quantity of 1 so it is a bit of a step backwards.





Your Answer





 2018-07-30         Fitch

how to group rows based on a dates in a single column

I have a range of date (A1:CY7026) with a column for start dates. this column has a large amount of repeated dates within it. i need these dates group together based on the working week they are located in (eg. all values reading 16/7/18 - 22/07/18 would be one group and the following week would make up another). Use below in B2 cell (Suppose you had a header column) and drag to the rest. Sort to obtain the desired result.=CONCATENATE(YEAR(A2),"-",WEEKNUM(A2)) [XXX]
 excel                     1 answers                     17 view
 2018-07-30         Regina

VBA VLookUp Runtime Error

I am doing a looping vlookup on the same worksheet but I am getting Runtime Error: 1004: Unable to get the Vlookup property of the WorksheetFunction class with this code:Sub Test() Dim d As Long Dim lastrowx2 As Long lastrowx2 = Worksheets("WS").Cells(Rows.Count, 1).End(xlUp).Row For d = 2 To lastrowx2 Worksheets("WS").Cells(d, 16).Value = _ 'cell where the lookup value will be placed Application.WorksheetFunction.VLookup( _ 'vlookup function Worksheets("WS").Cells(d, 15).Value, _ 'value to look for Worksheets("WS").Range("X...
 excel                     2 answers                     18 view
 2018-07-30         Moira

Create a new file and delete password protection

I use the following VBA to save a new file on my desktop:Sub Files()ActiveWorkbook.SaveCopyAs "C:\Users\" & Environ("Username") & "\Desktop\testfile.xlsm"Workbooks.Open "C:\Users\" & Environ("Username") & "\Desktop\" & "testfile.xlsm", UpdateLinks:=FalseMsgBox ("File saved successfully on desktop.")ThisWorkbook.Close SaveChanges = FalseEnd SubAll this works fine so far.My original file is proteced with a password. This protection should be deleted in the new file which is created using the VBA above.For unprotecting the file I have the following VBA:Sub ...
 excel                     2 answers                     20 view