Forum Discussion
Lookup / Index / Match formula
Hi,
Hope someone here could help me with a formula which I think could be a Index/match/lookup formula.
I have static conditions in columns F:H. These are always true together otherwise not.
If the information in column A & B match the information in column F & G I would like H to be displayed in C.
Anyone have a clue? Example file attached.
Twifoo for example?
Kind regards,
Viktor
With some assumptions that could be one of
=IFNA(LOOKUP(2,1/(A2=$F$2:$F$9)/($G$2:$G$9=B2),$H$2:$H$9),"") or =IFNA(INDEX($H$2:$H$9,MATCH(1,INDEX((A2=$F$2:$F$9)*($G$2:$G$9=B2),0),0)),"") or =XLOOKUP(A2&B2,$F$2:$F$9&$G$2:$G$9,$H$2:$H$9,"")or like
11 Replies
I added a column for C+M1 and C+M2 just to combine both columns in one field(you can hide the two columns) so it would be more easier to do a VLOOKUP table. Hopefully, this is what you wanted as your output in column H which is the value itself.
With some assumptions that could be one of
=IFNA(LOOKUP(2,1/(A2=$F$2:$F$9)/($G$2:$G$9=B2),$H$2:$H$9),"") or =IFNA(INDEX($H$2:$H$9,MATCH(1,INDEX((A2=$F$2:$F$9)*($G$2:$G$9=B2),0),0)),"") or =XLOOKUP(A2&B2,$F$2:$F$9&$G$2:$G$9,$H$2:$H$9,"")or like
SergeiBaklan Truly amazing. It worked perfectly! This is so much appreciated. Thanks for making my night! 🙂
viktor2170 , you are welcome
Since you have two criteria and the desired return is a number, you may use SUMIFS. This is presuming you want to total the amounts if two criteria appear together in multiple lines.
- Sorry about that. Example file attached below.
Building a bit on the comment by Patrick2788 , it's not really clear what this is going to look like in full production mode. I think what you've got here (correct me if I'm wrong) is just a hypothetical ("strawman") example laid out in a manner meant to reflect what you're really working with.
But what isn't clear, as Patrick2788 hints, are such question as:
- Is the "correct combination" going to appear only once?
- How many actual entries will you be ending up with in columns A & B, and F & G...
- Will A always correspond to entries in F (and vice versa), B with G, or will they be more random?
- Where do the "Price" figures come from?
I can think of some of the Database functions as working to deliver your answers (DGET, DSUM, etc), but whether those are the appropriate answers depends a lot on what you're really trying to do.
