You can make a difference in the Apple Support Community!

When you sign up with your Apple Account, you can provide valuable feedback to other community members by upvoting helpful replies and User Tips.

Looks like no one’s replied in a while. To start the conversation again, simply ask a new question.

Tracy E Author

Level 1

145 points

If a field contains X, check another table as to what to do...

I want to have a formula look to a different table to calculate a result as in this example:

In PROJECT CALCULATOR I want to calculate column 3, total charge for a crew member, based on the information typed into column 1 and 2.

The BILLING RATES table holds their hourly rates.

I want to type a name into column 1 and a number of hours into column 2. I then want the formula to figure out column 2 by looking at col 1 to see who's name is there, then refer to the BILLING RATES table to find their rate, then use that rate to multiply it by col 2, thus displaying the total.

Hope this makes sense.

Posted on Jan 28, 2021 2:01 PM

Top-ranking reply

Barry

Level 9

59,951 points

Posted on Jan 28, 2021 3:06 PM

PRoject Calculator::B2

Enter the formula shown below the table (and repeated below) in Project Calculator::C2 and fill down to the last row of column C.

IF(OR(A2="",B2=""),"",VLOOKUP(A2,BILLING RATES::A:B,2,FALSE)*B2)

The part in bold is the core formula that gets the billing rates and multiplies then by the number of hours in column B.

The IF statement is a wrapper that prevents calculation until there is a value in column A and B of 'this row'.

Numbers will change the multiplication operator ( * ) to a multiplication sign (× ) as soon as it is entered, but will not recognize a multiplication sign entered as part of the formula.

Regards,

Barry

View in context

If a field contains X, check another table as to what to do...

Similar questions