Help with Combination of Vlookup, Hlookup and Index's

[Mkammari]

On a userform, I am trying to have a text box automatically populate based on 3 drop down boxes. I am not sure what kind of method would work best and I have not been able to get this to work. Here is my dilema.

Drop down box 1......Ship From Location
Drop Down box 2......Ship to Location
Drop Down box 3......Weight
TxtFreightDollars....(answer I am seeking)


In the text box I am looking for a return value based on a worksheet I have set up for Freight values. The data is set up like this on my worksheet

Ship from Location Range A11:A148
Ship to Location Range B11:B148
Weights Range C10:V10
Freight Dollars Range C11:V148 (This is the value I want returned)

So in summary. I need a freight value returned based on three conditions (Ship From, Ship To, and Weight). The value will automatically populate in a text box. Hope this is all clear.

Michael
[/url]

 

[Mkammari]

if this is too vague...please let me know. This is crucial to my project.

 

[Norie]

Michael

You don't really need any of those functions for this as far as I can see.

The 3 dropdowns will return ListIndexs which you could use to get a value from the Freight Dollars Range.

It all really depends how that range is set up.

Where are the locations, weights etc?

Can you post an example of the data and required result, perhaps using Colo's HTML Maker?

 

[Mkammari]

Norie....I am getting an error when I try to convert the sheet to html stating "Compile error, cannot find project library"...should I download again.

 

[Mkammari]

The data is set up similiar to an x-y type format. I'll type an example of it below



weights
100 200 300 400 500

ShipFrom Ship To
Texas Maine 400 600 500 600 700
Californ. Arizona 400 900 700 400 300


Ship from is in Colum a..range a11:A148
Ship To is in colum b range B11:B148
weights are in column c7 thru colum v7:

The freight dollars are in range C8 thru v148

The are all defined in a list called freight.

Teh

 

[fairwinds]

Hi,

Try:

=INDEX(C5:G8,MATCH(J2&"@"&J3,A5:A8&"@"&B5:B8,0),MATCH(J4,C2:G2))
This formula needs to be confirmed with Ctrl + shift + enter.

Note that I changed the weights to enable an easier formula.

0 in C2 means 0 to 99.99999999 Kg
100 in D2 means 100 to 199.99999999 Kg

etc.

Book2
	A	B	C	D	E	F	G	H	I	J	K
1					weights
2			0	100	200	300	400		From	Californ.
3									To	Arizona
4	ShipFrom	ShipTo							Weight	350
5	Texas	Maine	400	600	500	600	700		Result	90
6	Texas	Arizona	400	900	700	400	300
7	Californ.	Maine	10	20	30	40	50
8	Californ.	Arizona	60	70	80	90	100
9
Sheet2

 

[Mkammari]

that looks like it may work..I'll give it a try.

 

[Mkammari]

I was so excited..but it returned a #Value.

But the layout is precisely what I am working with.

 

[Mkammari]

Norie...if you're out there. The layout that is provided by the person who attempted to help me is exactly how my spreadsheet is set up.

 

[Mkammari]

Bump..