-2

I need to multiply every unique ID intervals by corresponding interval in Table 2 according to date. Is there a way a formula I can use to do this quickly? This is just a small snapshot in other cases I would have hundreds of IDs and more than three dates.

SHEET 1

ID DATE INT1 INT2
100515 1/1/2020 .084 .078
100515 1/2/2020 .044 .078
100515 1/3/2020 .024 .078
125656 1/1/2020 .017 .078
125656 1/2/2020 .074 .078
125656 1/3/2020 .014 .078
189548 1/1/2020 .044 .078
189548 1/2/2020 .564 .078
189548 1/3/2020 .244 .078

SHEET 2

DATE INT1 INT2
1/1/2020 1200 566
1/2/2020 987 1822
1/3/2020 1602 1666

OUTPUT SHEET

ID DATE INT1 INT2
100515 1/1/2020 RESULT RESULT
100515 1/2/2020 RESULT RESULT
100515 1/3/2020 RESULT RESULT
125656 1/1/2020 RESULT RESULT
125656 1/2/2020 RESULT RESULT
125656 1/3/2020 RESULT RESULT
189548 1/1/2020 RESULT RESULT
189548 1/2/2020 RESULT RESULT
189548 1/3/2020 RESULT RESULT
Waterbottle
  • 17
  • 5
  • No, VLOOKUP would take too long as I would have to configure the formula to take the correct date and interval value in Table 2 for every unique ID – Waterbottle Aug 06 '21 at 19:08
  • Ok. Done. The sheets are uneven so it's not a simple vlookup as it would only drag to the third row for the first id. – Waterbottle Aug 06 '21 at 19:18
  • I'm not seeing why this is not a simple VLOOKUP. You are just multiplying the value in each row in table 1 by the value corresponding to given date in table 2, right? Where does the ID come in? Can you give the actual results instead of ".RESULT"? – EDS Aug 06 '21 at 19:29
  • Can you give a formula? Vlookup alone would be simple for a singular id but i cannot drag that formula down to other ids as there are uneven number of rows in sheet 1 and 2 – Waterbottle Aug 06 '21 at 19:31
  • So the INT1 results would be 100.8, 43.428, 38.448, 20.4, etc - is that right? Columns ID and DATE will always match perfectly across Sheet 1 and the Output Sheet? This feels solvable with a vlookup if I'm understanding it correctly. – Robson Aug 06 '21 at 19:31
  • That's the correct output. If I vlookup unique id it only gets one id. – Waterbottle Aug 06 '21 at 19:33
  • That's okay, we can do that part as well. I need to go out for a couple of hours, but if nobody else has solved this, I'll write it up when I'm back. – Robson Aug 06 '21 at 19:35

2 Answers2

1

Just use VLOOKUP. For example:

=VLOOKUP($G2, $A$13:$C$15,2,0)*C2

enter image description here

Then in column I replace the 2 with 3.

EDS
  • 2,155
  • 1
  • 6
  • 21
  • Can you add sheet name? What is G2 and a13:c15? – Waterbottle Aug 06 '21 at 19:38
  • 1
    If you look in the image, $A$13:$C$15 is what you call "Sheet 2" in your question. G2 is what you call INT1 in "Sheet 1" in your question. If you are literally referring to different sheets, just preface each range by the sheet name. For example, ```=VLOOKUP($G2, Sheet2!$A$13:$C$15,2,0)*Sheet1!C2``` – EDS Aug 06 '21 at 19:42
  • 1
    @Waterbottle if you cannot see the image than any answer will be hard to interpret. You will need to get on a computer that can see the images as that makes the formula make sense. – Scott Craner Aug 06 '21 at 19:50
  • @Waterbottle sorry, I meant that $G2 is what you call the date in the output sheet. You are looking up the date, not the ID. So without cell references, ```=VLOOKUP(output_date, Sheet2_frozen_range, 2,0)*Sheet1_Int``` – EDS Aug 06 '21 at 19:56
0

use vlookup and SUMIFS to return the correct values:

=SUMIFS(C:C,$A:$A,$A2,$B:$B,$B2)*VLOOKUP($B2,Sheet2!$A:$C,COLUMN(B1),FALSE)

enter image description here

Edit to include sheetname given and assuming A1 orientation:

=SUMIFS(Sheet1!C:C,Sheet1!$A:$A,$A2,Sheet1!$B:$B,$B2)*VLOOKUP($B2,Sheet2!$A:$C,COLUMN(B1),FALSE)

Put this in C2 of the output sheet, copy over 1 and down the dataset.

Scott Craner
  • 148,073
  • 10
  • 49
  • 81
  • Every unique ID would need to be multiplied by the values in Sheet2 corresponding with date. That would take too long. – Waterbottle Aug 06 '21 at 19:13
  • is that not what I did. `.084*1200 = 100.8`? If this is not what you want then you need include a lot more information in your question including expected output in with the data you have provided. – Scott Craner Aug 06 '21 at 19:15
  • I added the output. I would have to add that formula to every unique id and that would take way too long. These sheets are uneven so it would not work with VLOOKUP. – Waterbottle Aug 06 '21 at 19:17
  • Can you please add sheet name I am confused – Waterbottle Aug 06 '21 at 19:20
  • What is column B1? And wouldn't we need to lookup ID as each ID have different int values? – Waterbottle Aug 06 '21 at 20:07
  • it is just a counter, it does not matter to what worksheet it is pointed. I just want the `2` in the first use and when it is copied over one column it turns to `3`. It has nothing to do with the value in B1, just that it refers to the 2nd column. – Scott Craner Aug 06 '21 at 20:08
  • The SUMIFS is a way to do 2 column lookups. See [HERE](https://stackoverflow.com/questions/42492758/vlookup-using-2-columns-to-reference-another) It will return the correct value where the ID and Date match. – Scott Craner Aug 06 '21 at 20:10
  • So do I literally put COLUMN(B1), it is not coming from a sheet or anything right? – Waterbottle Aug 06 '21 at 20:14
  • correct. It does not matter, it is not looking for a value. `=COLUMN(B1)` will return `2` no matter to what sheet it refers or what value is in `B1`. – Scott Craner Aug 06 '21 at 20:15
  • This would work between sheets correct? I am getting !VALUE# – Waterbottle Aug 06 '21 at 20:25
  • If i wanted to multiply by another like Sheet2 how would I do that? I copyed the vlookup again but it says i entered too few arguments – Waterbottle Aug 06 '21 at 20:43
  • That is a new question. I am no longer by my computer. Ask a new question and someone will help you. – Scott Craner Aug 06 '21 at 21:49