Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple Criteria
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! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple Criteria
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,Sheet 1!A5:F5,0)) This is an *array* formula, hence commit with Shift+Ctrl+Enter. HTH Kostis Vezerides On Jun 7, 6:53 pm, Bhaider wrote: 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! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple Criteria
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! -- Becky "vezerid" wrote: 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,Sheet 1!A5:F5,0)) This is an *array* formula, hence commit with Shift+Ctrl+Enter. HTH Kostis Vezerides On Jun 7, 6:53 pm, Bhaider wrote: 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! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple Criteria
Apologies... I thought you knew $$.
=INDEX(Sheet1!$A$5:$R$56,MATCH(1,(Sheet1!$A$5:$A$6 5=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 On Jun 7, 11:01 pm, bhaider wrote: 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! -- Becky "vezerid" wrote: 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,Sheet 1!A5:F5,0)) This is an *array* formula, hence commit with Shift+Ctrl+Enter. HTH Kostis Vezerides On Jun 7, 6:53 pm, Bhaider wrote: 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! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple Criteria
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. -- Becky "Bhaider" wrote: 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Index & Match functions - multiple criteria and multiple results | Excel Worksheet Functions | |||
Multiple Sheet, Multiple Criteria Look-Up Function Help | Excel Worksheet Functions | |||
return multiple records matching multiple criteria | Excel Worksheet Functions | |||
Counting Cells with multiple criteria.One criteria supporting wild | Excel Worksheet Functions | |||
Counting Cells with multiple criteria.One criteria supporting wild | Excel Worksheet Functions |