Formula for multiple outcomes

Iv a spread sheet that changes daily. If things stayed in one place I can work it but as they don't I'm looking for a formula that can take in different possibilities.. today FSG (full size glass) would be in B2 but tomorrow HSG (half size glass) could be in that slot. If you need me to explain it more I will
6 Replies
We need more detail, lots of it!


As Mr. Doug_Robbins has informed you, more information is needed in order to be able to propose a solution that suits you.

If I may recommend it to you, tell us about the digital environment. Excel version, operating system, storage medium (hard drive, One-Drive, Sharepoint, etc.).

It would be best if you insert a file (without sensitive data) and explain your intentions step by step based on this file. Or insert photos with explanation.

Drag and drop here or browse files to attach
Maximum size: 71 MB • Maximum attachments allowed: 5

All this would be beneficial, especially for you, so you get a solution that fits your project/problem much faster and more accurately.


Here is a link with additional information: Welcome to your Excel discussion space!


Thank you for your understanding and patience



I know I don't know anything (Socrates)



It seems so that you have to apply array-formulas to get dynamic information about your data pool. To work with dynamic number of lines and/or columns you have to use dynamic defined named areas applying the function offset. The parameters may be fixed or dynamic defined named areas.

Good luck 

In square M5 I want to put a formula in it to show what I’m short to fill out my order. I can do the formula that will subtract what I have in stock and what I need but what I want to have in place is a formula that can look down column B and see FSG then match it with column F and give me what I need to order. My problem is. This table can change regularly so we’re FSG is now might not be SSG tomorrow. Is their a formula that can pick up FSG or SSG then find my total. Think what I’m looking for is something like.. IF FSG is in B5 and Full Size Glass is in C5 then show figures from F5. But what if FSG is in B8 and Full Size Glass is in F8 then F5 figures will not be what I need. I will need figures from F8.. so I need a formula to match FSG and Full Size Glass and its quantity needed. Bow is a small copy of what table I'm using.
best response confirmed by Norman_Glenn (Contributor)


Maybe as in the attached file.

Great reply. I'll give it a good look and see how it works but so far looks great thank you.