Match Data on list & extract

geeitsme

Board Regular
Joined
Mar 12, 2002
Messages
78
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
 

Some videos you may like

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
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

Well-known Member
Joined
Jan 20, 2004
Messages
3,459
Is your drop down list based on validation, or a combo box?
 

geeitsme

Board Regular
Joined
Mar 12, 2002
Messages
78
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

Well-known Member
Joined
Jan 20, 2004
Messages
3,459

ADVERTISEMENT

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

Board Regular
Joined
Mar 12, 2002
Messages
78
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

Well-known Member
Joined
Jan 20, 2004
Messages
3,459

ADVERTISEMENT

:confused:

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!) :biggrin:
 

geeitsme

Board Regular
Joined
Mar 12, 2002
Messages
78
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

Legend
Joined
May 15, 2003
Messages
29,536
Unfortunately that's not much help. :unsure:

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

Board Regular
Joined
Mar 12, 2002
Messages
78
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]
 

Watch MrExcel Video

Forum statistics

Threads
1,123,477
Messages
5,601,893
Members
414,479
Latest member
Beau the dog

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top