IF formula with multiple If_True_Values & Multiple Conditions HELP!

So I have been trying al day, searching every forum topic, every youtube video and every tips and tricks sheet, starting to think it is not possible.

I am relatively new with excel but pick things up quite quick, but this has flummexed me.

So in as simple terms as I can put it i am trying to make it so that IF Job!C2:C100(Product name from list)=(is the same as)A3(Product name selected from list) it will then use the numberical value in B2 and subtract the numberical value chosen in Job!D2. That part I got fine with the formula =IF(Job!C2:C100=A3,B2-Job!D2,B2).

However, the complicated part is that I then want the formula to also be able to do this for B-Job!D3:D100. But only if A3(the correct product name) is selected in that row.

I also want this to work so that it could also work in this way too =IF(Job!C2:C100=A4,B3-Job!D2,B3).

And so on. Sorry if this didnt make sense, I am a bit of a novice but the problem is that there are just so many variations that the spreadsheet needs to account for. I have tried using AND/OR in the formula I have tried =IF(Job!C2:C100=A3,B2-Job!, IF=IF(Job!C2:C100=A3,B2-Job!D3,B2)) just to try and get at least one product working with differed rows in Jobs!D.

And just incase it helps for me to describe why im doing this and the outcome i want. It is basically that we are a carpet fitting firm, and I want our stock to reduce on the spreadsheet everytime stock is taken out for a fitting job. so the conditions are i want to be able to select a product from a drop down list, that relates to a different page on the spreadsheet, this then uses the quantity ive input to reduce the quantity of the stock.

I tried to attached a screenshot of the spreadsheet im using to help you visualise the issue, but as its my first time posting i was unable to

Thanks in advance for your answers, looking forward to your responses.

Hi
are you in need of an Excel solution or a Google sheets solution ( you posted in an Excel subforum your profile indicates GSheets)
AFAIK the syntax =IF(Job!C2:C100=A3,... is not correct in Excel. Perhaps it is in GS?

Welcome to the forum.

Screenshots are not much use to us, as no-one wants to type in all your data in order to try a few things out. It is better to attach a sample Excel workbook. Details of how to do this are given in the yellow banner at the top of the screen.

Pete

I agree with Pete: you should attach an example Excel file that demonstrates the problem, so that we can see all data and formulas. And when you do, be sure to tell us which cells to look at in order to focus our attention on the problem.

And I agree with Pepe: =IF(Job!C2:C100=A3,B2-Job!D2,B2) is not valid syntax. That is, it would produce a #VALUE error, if it is entered in row other than 2:100.

Unfortunately, the formula might give you the illusion of "working" because it returns a value, not an error, when the formula is entered in rows 2:100 -- a consequence of the "implied intersection" feature. But it is important to understand that the value is probably not what you intended, except by accident.

Be that as it may, I wonder if the following does what you intended.

=IF(ISNUMBER(MATCH(A3, Job!$C$2:$C$100, 0)), B2-Job!D2, B2)

-----

Originally Posted by ReyLex54

However, the complicated part is that I then want the formula to also be able to do this for B-Job!D3:D100. But only if A3(the correct product name) is selected in that row.
[....]
Sorry if this didnt make sense
[....]
I have tried =IF(Job!C2:C100=A3,B2-Job!, IF=IF(Job!C2:C100=A3,B2-Job!D3,B2))

Sorry, but your requirement is unclear, and your attempt to implement it does not make sense, even after we correct the typos, to wit: (a) the first "B2-Job!" should be B2-Job!D2; and (b) remove "IF=".

The general form of an IF expression is IF(condition, value-if-true, value-if-false). It reads like: if "condition" is true, return value-if-true; otherwise, return value-if-false.

That is, it's an "either-or" operation. In general, a formula cannot return two results -- unless you intend to combine them arithmetically (e.g. add them), or you concatenate them as text.

But without a better description of __exactly__ how you want the result to appear, I cannot offer guidance without risking misdirection.

Originally Posted by curiouscat408

But without a better description of __exactly__ how you want the result to appear, I cannot offer guidance without risking misdirection.

Okay so what i want the spreadsheet to do is everytime someone selects a product(doorbar) for a different client in the Jobs page, I want it to reduce the stock amount of the selected product in the doorbars page.

So the sheet needs to be able to understand that if ive selected "Antique bronze tops fab tile edge 90cm" in Jobs!C2 and then put 1 into the quantity in the column next to it, it has to reduce the quantity level next to "Antique bronze tops fab tile edge 90cm" in Doorbars!C8.

and this then needs to work in the same way again, and for all the different types of door bars on for the next row down in Jobs!C3 etc.

So i can constantly add new clients to my jobs page, assign a product and quantity needed for that job, and then that automatically reduces the stock level for that product in the correct page.

Im not sure how to make it any clearer than that im afraid.

Let me know if that helps you understand my request better. as I say im a novice at excel so dont know if im going along the right lines with the IF formula, i simply went that route because when i said it outloud it was "if i select product X for a job I want to THEN reduce product X in the stock list"