Extract Data from Matrix

**woebegone** · 06-22-2008, 07:53 PM

Greetings:

I'm sorry to just jump in with my problem, but I'm not even sure what to call what I'm trying to do, so just let me ask...

I'm running a small messenger company and I've got a geographic area divided into five zones: 1,2,3E,3W,& 4

Deliveries within 1 zone will be charged for one zone.
Deliveries from Zone 1 to Zone 2 will be charged for 2 zones. Etc.

I've got a list of zones in my From column and also in my To column.

I need to write a formula that will give my customers the Zones Charged in a seperate cell.

I learned how to do something like this back in school, but don't even know what to call it anymore. Maybe it's some kind of comples IF function.

I'd sure appreciate anyone who can help me figure out how to do this.

FREE Deliveries to anyone in Long Beach, CA to the winner!!!

I'm attaching a chart to show what I need done.

Thanx in advance.

Michael Doughty
M&M Messenger

**rylo** · 06-22-2008, 08:53 PM

Hi

Say your matrix as shown is in the range G1:L6. In B2 you have your From Zone, and in C2 you have your To Zone.

The formula: =INDEX(G1:L6,MATCH(B2,G1:G6),MATCH(C2,G1:L1))
should bring back the result from your matrix.

HTH

rylo

**woebegone** · 06-27-2008, 02:15 PM

YES! That was it! Thanx much!

Woe

**woebegone** · 06-27-2008, 05:12 PM

I tried to repeat what (I thought) I'd learned, but this time it's not working.

In cell C36 I have a dropdown list of possible services, Direct, Standard, Same Day, and Overnight.

In cell C37 I have the results of the above matrix calculation 1,2,3, or 4.

It seems like to me that the matrix (See attached) I'm employing should render the resulting prices from the table, but it's not working.

What am I doing wrong?

Wish I'd paid more attention while in school!!

Woebegone

**rylo** · 06-27-2008, 06:19 PM

Hi

Can you please attach a copy of the workbook rather than a picture. Much easier to see what is going on and we don't have to reenter formulas / data.

rylo

**woebegone** · 06-27-2008, 06:26 PM

Here it is:

Thanx.

Woe

**rylo** · 06-27-2008, 07:22 PM

Hi

The formula in C37 should be =INDEX(AH1:AM6,MATCH(B24,AH1:AH6),MATCH(F24,AH1:AH6))
to bring back the number of zones.

Where is the formula for the price supposed to go? C38??? And is it supposed to be based on the results from C36 and C37?

rylo

**woebegone** · 06-27-2008, 07:51 PM

Yes, C38, for now at least. Yes C37, Service Type and C38, Zones charged needs to compute the price subtotal in C38

Woe

**rylo** · 06-27-2008, 08:06 PM

Hi

C38: =INDEX(AH8:AL12,MATCH(C36,AH8:AH12,0),MATCH(C37,AH8:AL8,0))

For your example items of Standard and 4 zones, the result is N/A.

rylo

**woebegone** · 06-27-2008, 09:25 PM

Perfect! I really appreciate all your help. If you're ever in Long Beach, I owe you a beer!

Thanx Again,

Woe

**woebegone** · 08-08-2008, 01:48 PM

The solution rylo gave me for this problem looked like it was working, but I've discovered a problem.

The formula seems to extract the right data from the matrix except when C36="Direct", then it returns "N/A". It should be returning data from AH9;AL9. The matrix table is correct, and the formula works in every other instance. Perhaps I'm missing something in the formula.

Could someone take a look and see what it is I've screwed up?

Thanx in advance

Woe

**Ron Coderre** · 08-08-2008, 02:35 PM

Please Login or Register  to view this content.

Edit the space out of AD2 and the formula should work properly.

Does that help?

**woebegone** · 08-08-2008, 04:33 PM

Yup...

Now I feel silly. Thanx!

Extract Data from Matrix

LinkBack

Thread Tools

Rate This Thread

Display

Extract Data from Matrix

Thank You!

Why can't I repeat this?

Thanx Again!

Extract Data from Matrix... a small problem.

Extract Data from Matrix

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions