Help...Function Challenge!!...VERY IMPORTANT FOR MY JOB

[cecilluen]

Please, see the attached file. I need this for an inventory system.
There are 2 tables. One has the criteria needed.
Where it says CYCLE, means, the items in order of importance (meaning
the most important).
DIVISION: means if the vendor is domestic or international.
LEAD TIME: means the time that it takes for a vendor to deliver th
product (in months).

What I'm trying to get is a formula that will give me the LEAD TIME
automatically when I already have the ITEMS CYCLES (A,B,C, etc) and th
DIVISIONs.
Please, help me!!!!
thanks

Attachment filename: problema.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=65660

 

[Max]

One way:

Put in C11, and array-enter* the formula:
=INDEX($C$2:$C$7,MATCH(1,($A$2:$A$7=A11)*($B$2:$B$7=B11)+0,0))

*Press CTRL+SHIFT+ENTER
(instead of just pressing ENTER)

Copy C11 down

 

[Max]

To effectively tap the resources of these excel newsgroups:

a. Post only in plain text, *one* question per post, with sufficient
descriptives of your set-up, current formulas used (if any) and intent.
Describe some sample data and the desired results where possible.
(See an example plain text description of your post below)

b. Do *not* post any attachments, *nor* rely on providing links to files
as a substitute for (a). Many will not download files for obvious reasons.

c. Be patient, do not repost as a fresh thread barely 2 hours on.
Not every post can be answered almost immediately.

Do read Chip Pearson's posting guidelines for new posters:
http://www.cpearson.com/excel/newposte.htm

-------------
An example of a plain text description of your post:

In Sheet1
-------------
In A1:C7 is a reference table:

CYCLE__Division__Lead Time
A_____Domestic_____1.75
A_____International__5
B_____Domestic_____1.5
B_____International__4.5
C_____Domestic_____1
C_____International__4

In A10:C18 say, data from row11 down
you have a list of CYCLEs and Divisions for which you
want to extract the Lead Times from the table above

CYCLE__Division__Lead Time
A_____Domestic_____?
A_____International__?
A_____Domestic_____?
B_____International__?
B_____Domestic_____?
C_____Domestic_____?
C_____International__?
C_____International__?

What formulas could be used to extract the Lead Times?

 

[cecilluen]

can you explain me the match function?...please

 

[Max]

First things first, hope you got the suggestion to work?

Btw, the "+0" part in the suggested formula wasn't required, sorry ..
so we could have just array-entered in C11:
=INDEX($C$2:$C$7,MATCH(1,($A$2:$A$7=A11)*($B$2:$B$7=B11),0))
and copied down

When you array-enter the formula (with CTRL+SHIFT+ENTER),
Excel will wrap the formula within curly braces { }
(Do not enter these braces yourself !)

And remember to *array-enter* again
whenever/if you edit the formula to suit
-----

Ok, ... perhaps you should try reading the explanation
in Excel's Help on MATCH() ..

The syntax is: MATCH(lookup_value,lookup_array,match_type)

So in C11's: .. MATCH(1,($A$2:$A$7=A11)*($B$2:$B$7=B11),0) ..
we'll have:

Lookup_value: = 1
Lookup_array: ($A$2:$A$7=A11)*($B$2:$B$7=B11)
Match_type: 0 (or FALSE, meaning find an exact match,
"0" is the usual lazy way used, less to type)

The Lookup_array resulting from the product of :
($A$2:$A$7=A11)*($B$2:$B$7=B11)
will provide the desired "matching" of the CYCLE and the Division
in the reference table for the values in A11 and B11

This Lookup_array will resolve to: {1;0;0;0;0;0}
for the lookup_value: 1
to find an exact match (match_type: 0)

If you carefully highlight the lookup_array part in the formula bar:
($A$2:$A$7=A11)*($B$2:$B$7=B11)
and then press F9, you'll see it resolves to {1;0;0;0;0;0}
(Press Esc to revert)

The result returned by MATCH(...) above in C11 will hence be: 1
which is then used in INDEX($C$2:$C$7, ... ) to return
the first value in the array $C$2:$C$7, i.e.: 1.75

In C12, MATCH(...) resolves to 2, hence INDEX(...) returns
the 2nd value in the array $C$2:$C$7, i.e.: 5

And so on ..