Skip navigation

Formula: Multiply if...

275 Views 5 Replies Latest reply: Nov 4, 2013 5:18 PM by SGIII RSS
papalapapp Level 1 Level 1 (80 points)
Currently Being Moderated
Nov 4, 2013 3:06 PM

Hi,

 

I have two tables. One contains a list of pre-defined IDs. The other one is for data entry where the user enters records row by row including an ID

 

I'm trying to figure out how to multiply two cells in a record, if the user enters one of the IDs that is listed in the other table.

 

Now one could use IF and OR to match these cells, but I can only match one cell to one cell. As the list of IDs has about 100 entries, it would be good if I could match a cell against a whole column like in SUMIF.

 

Any ideas?

 

calc.png

MacBook, OS X Mavericks (10.9), 1TB HD & 256 GB SSD inside
  • Wayne Contello Level 6 Level 6 (12,650 points)
    Currently Being Moderated
    Nov 4, 2013 3:37 PM (in response to papalapapp)

    just use the match() function to see if it returns an error (not found condition):

     

    Screen Shot 2013-11-04 at 5.36.52 PM.png

    E2=IF(IFERROR(MATCH(B2,ID::A,0), 0)>0, D2, 1)×C2

     

    select E2, copy, select E2 thru the end of the column, paste

  • SGIII Level 4 Level 4 (3,290 points)
    Currently Being Moderated
    Nov 4, 2013 3:40 PM (in response to papalapapp)

    hi papalappa,

     

    Is this what you want?

     

    =IF(ISERROR(VLOOKUP(B2,Table 1::A,1,FALSE)),"",C2×D2)

    Screen Shot 2013-11-04 at 6.34.41 PM.png

    It tries to look up the value in B from the table containing the ID's and if it can't find that value (gets an error) it puts a blank in the cell. If it does find the value in the list of IDs, it multiplies C x D.

     

    I wasn't sure if that is exactly what you want because in your example there is no AAA in your list of IDs yet you still show a value in E.

     

    SG

  • SGIII Level 4 Level 4 (3,290 points)
    Currently Being Moderated
    Nov 4, 2013 5:18 PM (in response to papalapapp)

    Hi papalapapp,

     

    Yes, looks like basically the same idea, except that Wayne was faster and saw what you meant exactly.

     

    Where my formula has the "" you would substitute C2, I think, to get the same result.

     

    Thanks for the interesting problem. Keeps the mind sharp.

     

    SG

Actions

More Like This

  • Retrieving data ...

Bookmarked By (0)

Legend

  • This solved my question - 10 points
  • This helped me - 5 points
This site contains user submitted content, comments and opinions and is for informational purposes only. Apple disclaims any and all liability for the acts, omissions and conduct of any third parties in connection with or related to your use of the site. All postings and use of the content on this site are subject to the Apple Support Communities Terms of Use.