Multiple Criteria

[Bhaider]

This is on Sheet1 (database of prices):

A B C D E
F

5 Type I Type II
Type III
6 Plate 1/4" Standard .35 .40 .25
7 Plate 1/4" Cut 1.40 1.40
1.40
8 Flat 1/4" Standard .99 .75
..39
9 Square 1/4" Standard .55 .50 .45
10 Round 1/4" Standard 1.30 2.00 1.75

On the second page, I have four different drop down boxes in which they need
to choose 1) Cell H56: Type (I,II, or III), 2) Cell H57: Structure (plate,
flat, square, round), 3) Cell H58: Size (1/4", 1/2", etc.), and 4.) Cell H59:
Standard or Cut

Based on what they choose for the four above, I want to write a formula to
choose the price related to those selections. I have tried multiple formulae;
however, I can't seem to get it.

Below is the closest:

INDEX(Sheet1!A5:R56,((AND(MATCH(H57,Sheet1!A5:A57,0),MATCH(H58,Sheet1!B5:B57,0)))),(MATCH(H56,Sheet1!D5:R5,0)+3))

The "3" at the end is to move the column over 3 cells since my data range
(i.e. Type I) starts in Column D, not Column A.

The result of this formula is "Type I" (i.e. Row 5) not .35 (Row 6) (based
on: Type I, Plate, 1/4", Standard selections in the drop down boxes)

If I am way off base, I am open to other ideas/functions.

Thank you VERY MUCH for any assistance!

 

[vezerid]

Cell H56 will help identify the column, hence it should appear in the
3rd argument of INDEX. The other three, H57:H59, will determine the
row:

=INDEX(Sheet1!A5:R56,MATCH(1,(Sheet1!A5:A65=H57)*(Sheet1!
B5:B65=H58)*(Sheet1!C5:C65=H59),0),MATCH(H56,Sheet1!A5:F5,0))

This is an *array* formula, hence commit with Shift+Ctrl+Enter.

HTH
Kostis Vezerides

 

[bhaider]

Thank you SO MUCH for your help! It worked for the cell I am in but I was
wondering how to copy it to another cell? Also, just for my information, what
does the "1" represent in the first MATCH equation (i.e. "MATCH(1,))?

Thanks, again!

 

[vezerid]

Apologies... I thought you knew $$.

=INDEX(Sheet1!$A$5:$R$56,MATCH(1,(Sheet1!$A$5:$A$65=H57)*(Sheet1!$B
$5:$B$65=H58)*(Sheet1!$C$5:$C$65=H59),0),MATCH(H56,Sheet1!$A$5:$F
$5,0))

As you see all the references to the original table get full $$,
before letter AND number. This still leaves us with the question of
what $$ to put in the cell refs H56-H59.
If you are planning to have 4 columns with combinations of values and
then a 5th column with my formula, leave them as they are. If you want
another layout, be specific about the layout.

As to the 1. Notice that the 2nd argument of MATCH is the
multiplication of two conditionals (...)*(...). Each one of these can
be TRUE/FALSE. When multiplied they can be 1/0. We are looking for the
first 1 in the virtual array that is produced when we examine our
conditions against each member of the original array.

HTH
Kostis

 

[bhaider]

Thanks, again, for your help!! (Sorry for the delay in responding...I was on
vacation).

I did try the $ before but used F4. This time I manually typed the $ and it
worked.

Again, thanks.