TABLE1

0 A B C D E

1 PO CL ST DL OT

2 Lab 1 10 03 20

3 Lab 2 15 05 30

4 Lab 3 20 08 40TABLE2

10 A B C D

11 POS CLS TYP (FORMULA IN TABLE2, CELL D11)

IF POS=Lab, CLS=2, AND TYP=ST, THEN 15

IF POS=Lab, CLS=2, AND TYP=DL, THEN 05

IF POS=Lab, CLS=2, AND TYP=OT, THEN 30

D11=SUMPRODUCT(--($A$1:$A$4=$A$11),--($B$1:$B4=$B$11),--(

I’M STUCK, PLEASE HELP!

✔ Best Answer

Since we now know that Column A may contain different values than shown in your examples, it sounds to me like we first need to lookup the combination of A11 and B11 and find the Row that contains the matching set of values in Table 1 Columns A & B. After that, we need to lookup the value found in C11 in C1:E1 and see which Column it is in.

We can then use the Row number and Column number with the INDEX function to return the value at that "intersection".

=INDEX(array, row_num, column_num)

Here's what I would do:

I would use another column (e.g. F) and Concatenate the values in Table 1 Columns A and B. You can use any column you want and then hide it if you don't want to see it.

For example, in F2 enter this and then drag it down to F4:

=A2&B2

This will give you values like Lab10, Oper25, etc.

Then, assuming you used F2:F4 for your concatenated strings, use this formula in D11:

=INDEX($C$2:$E$4,MATCH(A11&B11,$F$2:$F$4,0),MATCH(C11,$C$1:$E$1,0))

The first MATCH function will lookup the combination of A11 and B11 in F2:F4 and return the Row number of the array where it was found. Note: The value that is returned is not the Row number of the Excel spreadsheet, but the Row number of the array F2:F4. i.e. F2 is Row 1, F3 is Row 2, etc.

The second MATCH function will lookup the value in C11 in C1:E1 and return the Column number of the array where it was found. Again, that's the Column number of the array C1:E1, not the column number of the spreadsheet. i.e. Column C is Column 1, etc.

Finally, the INDEX function will use the Row and Column numbers returned by the MATCH functions to return the value found at that intersection within the array C2:E4.

For example, if the MATCH functions returned Row 1 and Column 2, INDEX would return the value found in D2, since that is the cell at the intersection of Row 1 and Column 1 of the array C2:E4.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Please read the information found by clicking on the following line and then repost your data. Thanks!

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

I have tried to use "< PRE >< / PRE >" and it did not come out aligned as you have recommended. It worked as a "carriage return" instead. There is no "Preview Follow Up" or "Check To Show Confirmation Page Again" when editing. I need help on that cause this "Post Reply" is not as MS Word. Please advise. Mike

re: " I have tried to use "< PRE >< / PRE >" and it did not come out aligned as you have recommended"I don't see why not...it worked for me. (See below)

re: "

There is no "Preview Follow Up" or "Check To Show Confirmation Page Again" when editing."If you'll re-read my earlier response, you'll notice that I said "repost your data" not "edit your post". Had you re-posted your data as suggested, you would had access to the "Preview Follow Up" and "Check To Show Confirmation Page Again" options.

I've taken a shot at lining up your data (mainly to show you that it

canbe done) but of course I don't know if I've gotten it right.Even if I have lined it up correctly, I have to admit that I don't know what you asking for help with. Your subject line mentions an "If Then Statement" and you have some IF conditions listed, but you also included a partial SUMPRODUCT formula.

Are you asking us to construct an IF statement to meet the 3 conditions listed?

What is the partial SUMPRODUCT formula for?

TABLE1 A B C D E 1 PO CL ST DL OT 2 Lab 1 10 03 20 3 Lab 2 15 05 30 4 Lab 3 20 08 40 TABLE2 11 POS CLS TYP (FORMULA IN TABLE2, CELL D11)

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Thank you for your patience for I'm new at all this. Yes, Table 1 is correct.

I'm trying to construct an IF statement in Cell D11 (In Table 2) that would work in 3 different cases within the same cell:

If A11=A3, B11=B3, and C11=C1, Then answer is C3

If A11=A3, B11=B3, and C11=D1, Then answer is D3

If A11=A3, B11=B3, and C11=E1, Then answer is E3So the answer should look like for Row 11 in the following 3 cases:

11 Lab 2 ST 15

11 Lab 2 DL 05

11 Lab 2 OT 30

All 3 of your conditions require A11=A3 and B11=B3. If those are TRUE then the answer will be based on the contents of C11. What happens if A11 <> A3 and/or B11 <> B3? What should the answer be in those cases?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

The answer to your first statement is yes. If A11=A2, B11=B2, and C11=C1, Then answer is C2

If A11=A2, B11=B2, and C11=D1, Then answer is D2

If A11=A2, B11=B2, and C11=E1, Then answer is E2So the answer should look like for Row 11 in the following 3 cases:

11 Lab 1 ST 10

11 Lab 1 DL 03

11 Lab 1 OT 20OR

If A11=A4, B11=B4, and C11=C1, Then answer is C4

If A11=A4, B11=B4, and C11=D1, Then answer is D4

If A11=A4, B11=B4, and C11=E1, Then answer is E4So the answer should look like for Row 11 in the following 3 cases:

11 Lab 3 ST 20

11 Lab 3 DL 08

11 Lab 3 OT 40

A2:A4 all contain "Lab". What happens if A11 doesn'tcontain "Lab"?

What happens if B11 doesn't contain a value from B2:B4?

What happens if C11 doesn't contain a value from C1:E1?

My main point here is that you've given us requirements for a few specific sets of conditions, but you haven't told us what the answer should be if none of those conditions are met.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

A2:AX contains all classifications like: Laborers, Operators, Carpenters, etc. If A11 (classification) and B11 (group) happen to be "Lab 10", "Oper 25" or "Carp 4" then the values under columns C, D & E would show the rates (ST, DL or OT) or whatever values listed in Table 1, Columns C, D & E.

Since we now know that Column A may contain different values than shown in your examples, it sounds to me like we first need to lookup the combination of A11 and B11 and find the Row that contains the matching set of values in Table 1 Columns A & B. After that, we need to lookup the value found in C11 in C1:E1 and see which Column it is in.

We can then use the Row number and Column number with the INDEX function to return the value at that "intersection".

=INDEX(array, row_num, column_num)

Here's what I would do:

I would use another column (e.g. F) and Concatenate the values in Table 1 Columns A and B. You can use any column you want and then hide it if you don't want to see it.

For example, in F2 enter this and then drag it down to F4:

=A2&B2

This will give you values like Lab10, Oper25, etc.

Then, assuming you used F2:F4 for your concatenated strings, use this formula in D11:

=INDEX($C$2:$E$4,MATCH(A11&B11,$F$2:$F$4,0),MATCH(C11,$C$1:$E$1,0))

The first MATCH function will lookup the combination of A11 and B11 in F2:F4 and return the Row number of the array where it was found. Note: The value that is returned is not the Row number of the Excel spreadsheet, but the Row number of the array F2:F4. i.e. F2 is Row 1, F3 is Row 2, etc.

The second MATCH function will lookup the value in C11 in C1:E1 and return the Column number of the array where it was found. Again, that's the Column number of the array C1:E1, not the column number of the spreadsheet. i.e. Column C is Column 1, etc.

Finally, the INDEX function will use the Row and Column numbers returned by the MATCH functions to return the value found at that intersection within the array C2:E4.

For example, if the MATCH functions returned Row 1 and Column 2, INDEX would return the value found in D2, since that is the cell at the intersection of Row 1 and Column 1 of the array C2:E4.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

I got it. It works great. Thanks for your help. The world is a wonderful place to have helpers like you. I know my boss and many co-workers will be happy. Thanks a million! Mike

I'm glad I could help. One last comment...

As you can see from this thread, it took a while to just nail down the requirements. As soon as I understood all of the various inputs and outputs, it didn't take very long to come up with a solution.

The more information and clarity you can provide upfront, especially by posting examples of input and the desired output, the quicker a solution can be found.

Always remember that we can't see your spreadsheet from where we're sitting, nor can we read minds, so the only way for us to know what you are trying to accomplish is for you to tell us - in explicit detail.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

I have another question regarding this same If Then statement. If the answer does not match what's on table 1, then it shows "#N/A". Can I edit it so that it shows "0" or blank in D11? I need these answers to be zeros or blanks because they will be used in products and subtotal. If they are errors due to missing or unmatched data then they cannot be multiplied or added.

The ISNA function checks for a #N/A error as the result of a formula. By using IF(ISNA(

your formula) you can "capture" the #N/A error and force a different result:=IF(ISNA(INDEX($C$2:$E$4,MATCH(A11&B11,$F$2:$F$4,0),MATCH(C11,$C$1:$E$1,0))), "Whatever you want to show for a #N/A error", INDEX($C$2:$E$4,MATCH(A11&B11,$F$2:$F$4,0),MATCH(C11,$C$1:$E$1,0)))

You can avoid #N/A errors if you use Data Validation Drop Down lists populated with only the values found in your table.

That forces the user to choose only valid entries.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Great, it worked! Another great answer and solution. Thanks so much for your help! Mike

Ask Your Question

Weekly Poll

Do you think Microsoft's new Surface Go will be a hit?

Discuss in The Lounge

Poll History