SOLVED

Lookup / Index / Match formula

Copper Contributor

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

11 Replies

@viktor2170 

 

Example file was not attached. Please add it to the thread...

Sorry about that. Example file attached below.

@viktor2170 

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.

@viktor2170 

 

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.

best response confirmed by viktor2170 (Copper Contributor)
Solution

@viktor2170 

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

Thanks for yor input Patrick2788 & @mathetes!

 

The example file should have been clearer I agree. I´ve updated it and attached it. What I´m looking at is three countries: Denmark, Norway & Sweden (Column A). Each country can have 12 different values each depending on which month we´re in. So 36 entries in F, 36 in G and 36 in H. 

 

@mathetes 

 

  • Is the "correct combination" going to appear only once?

- The correct combination can only relate to a value. This is given every time A is filled out with a country and match with a country in F and every time a country filled out in B match with a month in G. Since that specific match (country & month) relates to a value in H - that value should appear in C.  

  • How many actual entries will you be ending up with in columns A & B, and F & G...

- A & B will only be filled once at a time, when there is a need to check "which price does Norway have in March" and then displayed in C. So these she be formula free columns. F will have 36 entries and G will have 36 entries. H will have 36 entries as well. 

  • Will A always correspond to entries in F (and vice versa), B with G, or will they be more random?

- Yes, A will always correspond to F and B will always correspond with G. 

  • Where do the "Price" figures come from?

- Price figures can vary. So it could be possible for example to change the value in H3. F & G will always be the same. 

 

Many thanks for you support on this! 

@Sergei Baklan Truly amazing. It worked perfectly! This is so much appreciated. Thanks for making my night! :) 

@viktor2170 

In addition to above, one more variant

=IFERROR(FILTER($H$2:$H$37,($F$2:$F$37=$A3)*($G$2:$G$37=$B3)),"")

 

@viktor2170 , you are welcome

@viktor2170 

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.

1 best response

Accepted Solutions
best response confirmed by viktor2170 (Copper Contributor)
Solution

@viktor2170 

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

View solution in original post