Formula: Multiply if...

Question

Level 1

119 points

Formula: Multiply if...

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?

MacBook, OS X Mavericks (10.9), 1TB HD & 256 GB SSD inside

Posted on Nov 4, 2013 3:06 PM

Reply

Answer 1

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):

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

Reply

Answer 2

SGIII

Level 8

37,562 points

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)

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

Reply