Creating a validation list then using a hlookup to pull info on the item in the drop down

Hi,

I have the below items in a list horizontal in excel with various metrics under each BL code.

I want to use the horizontal list as my data validation (validation criteria = List) and then have another cell that has a hlookup to the dropdown list and pulls data from the cells with the various metrics.
so if I enter the following formula I will get 6 as my answer.


HLOOKUP($C$10,Sheet2!$A$17:$Z$42,2)

1 BL20001
2 6
3 3
4 3
5 3
6 3
7 0.5 W PP
8 0.5 W PP
9 2 W PP


The hlookup does not seem to be pulling the correct info, it's like it does not recognize the difference in the data validation list.

Thank you!

Hi - I attached the spreadsheet.

Thank you!

Hi PS,

On Sheet1 you have a situation where you either have four formula errors or four formula omissions... (These have nothing to do with table lookups - these are just straight calculations). In addition, all of your HLOOKUP formula need to include the 4th parameter of "FALSE" to force an exact match in the lookup process.

I've re-attached a modified version of your posted workbook with the following changes:

1) In cells where HLOOKUP used, fourth parameter of "FALSE" added
2) In cells highlighted in pink, you have formulae that reference blank cells as one of the factors in calculations (see inserted comments)
3) In cells highlighted in orange, are those blank cells referred to in the pink-highlighted cells (see inserted comments)

I'm afraid I cannot comment on the "errors" you mention in the OP until you've fixed these errors above and - if possible - added some row headings for the table on Sheet2 (its almost impossible to follow the logic without understanding the name/function of the factors in the formulae).

As a general comment on "best practices" when using Excel, try to avoid using relative references and rather name cells, ranges and arrays using meaningful name values, then use the name in your formulae instead of the cell referencing. This allows anyone (even you in 6 months time) to more quickly understand the logic employed in the formulae.

By way of example:

Name cell Sheet1!C2 as "Selected_Product"
Name array Sheet2!C4:Z27 as "Product_Table"
Name range Sheet2!B4:27 with individual names - one per row (eg B6 = 2 named as "OzPerSqYd"

Now, when you create a formula using references for these values you currently wind up with something that looks like this:

HLOOKUP($C$2,Sheet2$C$4:$Z$27,2,FALSE)

Which, after 6 months of working on other spreadsheets doesn't mean much to you.

Using names, on the other hand, results in a formula that looks like this:

HLOOKUP(Selected_Product,Product_Table,OzPerSqYd,FALSE)

Allowing an interpretation to be: Lookup the Selected Product in the Product Table and return the Ounces/Square-Yard if lookup-match is exact

I have a simple rule:

If you have the time, name all critical cells/ranges/arrays and if you don't have the time, find the time!

In the long run, it saves headaches and hassles.

Best Regards,

Dave

PS: I almost forgot to attach the workbook:

Attachment 237837

PS I have removed the files as requested