Match Data on list & extract

[geeitsme]

Im using XL 2k and working on several worksheets. Im trying to create a formula to do the following.

On Hardware Wksht Tab cell B20 should extract data from HardwareLIST Wksht if it matches to cell C20 from Hardware Wksht Tab. The cell in HardwareLIST A10 is a drop down list of hardware.

C20 Specifies 4 x 8 Only
C21 Specifies 4 x 9 Only
C22 Specifies 4 x 10 Only

However the drop list contains 3 different types of 4x8's, 4x9's & 4x10s. If selected then B20 should read drop list and match criteria & return the value from HardwareLIST D10. Im having trouble formulating this one. Any hints?

Thanks
Gee

 

[Smitty]

Have you tried VLOOKUP? The help file has good detail on how to use it.

Posting a shot of your sheet will help as well.

Smitty

 

[tactps]

Is your drop down list based on validation, or a combo box?

 

[geeitsme]

I tried Vlookup and it returned the value however it pulled the 4x10's when it should have not. The list is a validation list.

Im assuming I am not formulating the lookup correctly.

suggestions.... ?

 

[tactps]

Have you included the "false" when you did your vlookup. If not, it will look for a "close' match not an exact match
=vlookup(lookup_value,table_array,col_index_num,false)

Post your formula so e can see it.

 

[geeitsme]

CellB20=VLOOKUP(Shear!A6,WoodSizes1stFloor,2,false)


A6 could be anything, 4x8, 4x9, 4x10

CellB20 should only add all in the list that could range from a6 to a52 that meets 4x8's and CellB21 should only add all in the list that could range from a6 to a52 that meets 4x9's etc.

Should I do a Sum formula?

 

[tactps]

Smitty is right - post your worksheet using Colo's download (see the bottom of the Web page).

It is easier for us to see what the issue is if we can see the issue (if this sentence makes any sense!)

 

[geeitsme]

HardwareWkst
ColB .............................ColC
ColB=all 4x8 =15............ColC 4'X8' SHEAR
ColB=all 4x9 =12............ColC4'X9' SHEAR
ColB=all 4x10 =16............ColC4'X10' SHEAR
----------------------------------------------------------------------------------
HardwareLIST Wksht
ColA......................Col D (Qty)
DropDownList
ColA 4x8x9............Col D (Qty)5
ColA 4x8x10............Col D (Qty)10
ColA 4x9x8............Col D (Qty)12
ColA 4x10x10 ............Col D (Qty)6
ColA 4x10x12............Col D (Qty)10
----------------------------------------------------------------------------------

 

[Smitty]

Unfortunately that's not much help.

The HTML Maker gives an EXACT shot of your sheet and it also represents your formulas which is very important to see why they're not doing what you want.

It's really easy to use. Download it, unzip it and place it in your Add-Ins folder. Activate it from the Add-Ins menu. You will then have a new menu item named HTML. From there, Select Used Range and follow the prompts. You can adjust what was selected with SHIFT+Arrow. Your sheet will appear in a new browser window, click the button at the bottom to send the image to the clipboard, return to your post and CTRL+V to paste the HTML code.

Then we'll be able to get you a solution quickly. :wink:

Smitty

 

[geeitsme]

I downloaded the addIn and I select Used Range and it highlights the columns and nothing else happens. No new browser is activated. : (

I am trying this formula which are not working unfortunately.
=SUMIF(Hardwarelist!A6:A26,"eig*",Hardwarelist!D6:D26) Note eig = eight
=SUMIF(Hardwarelist!A6:A26,"4x8*",Hardwarelist!D6:D26)
=SUMIF(Hardwarelist!A6:A26,"4x9*",Hardwarelist!D6:D26)

Any other suggestions?
[/img]