Spill formula to lookup matching row data in one sheet with column data in another and evaluate corresponding cells

0 votes

I have two sheets, one with month/year data spilt across the row from I2 using the formula 

=EDATE(DATE(2017,8,1), SEQUENCE(1,401,0))

 and another with month and expenditure data running down the columns.

I'd like to insert a spill formula in to I3 in the first sheet to lookup the month from I2# with the corresponding month in the second sheet and if found, multiply the values in columns D and E.

With the data being in rows on one sheet and columns in the other I'm not sure how to tackle this.

The following is a sample of the data in the second sheet:

Month Projected Costs Actual Costs Monthly Employees Monthly Per Employee Costs
Nov-19 £2,000 £3,000 5 £600
Dec-19 £10,000 11 £909
Jan-20 £18,000 19 £947
Feb-20 £18,000 19 £947
Mar-20 £18,000 19 £947
Apr-20 £18,000 19 £947
May-20 £18,000 19 £947
Jun-20 £18,000 19 £947
Jul-20 £18,000 19 £947
Aug-20 £18,000 19 £947
Sep-20 £18,000 19 £947
Oct-20 £18,000 19 £947
Jan 17, 2023 in Others by Kithuzzz
• 38,000 points
552 views

1 answer to this question.

0 votes

Your formula's output will match I2size #'s and dimensions if I2# is used correctly. One method would be to perform separate lookups on columns D and E, respectively, and multiply those results.

=XLOOKUP(I2#, Sheet1!A:A, Sheet1!D:D) * XLOOKUP(I2#, Sheet1!A:A, Sheet1!E:E)

(You might wish to add an IFERROR() function around the outside of that to clear up all the months where data isn't found)

answered Jan 17, 2023 by narikkadan
• 63,600 points

Related Questions In Others

0 votes
1 answer

Creating sheets with names in column B and assigning data to each sheet for particular name

after the first occurrence of Set sht = ...READ MORE

answered Feb 13, 2023 in Others by narikkadan
• 63,600 points
893 views
0 votes
0 answers

Data Driven Framework -- how to read and write in excel sheet using Selenium WebDriver with java

I'm using this code to read something, ...READ MORE

Oct 31, 2022 in Others by Kithuzzz
• 38,000 points
663 views
0 votes
1 answer

Excel - How can I get the average of cells where the value in one column is X and the value in another column is Y?

Use AVERAGEIFS ... =AVERAGEIFS(C2:C13,A2:A13,"Yellow Typ ...READ MORE

answered Nov 11, 2022 in Others by narikkadan
• 63,600 points
1,419 views
0 votes
1 answer

How do you populate a google sheets/excel column with cells from a column in another sheet in the same document?

You have two options on chronology: sheet-by-sheet =QUERY({Sheet1!X:Z; Sheet2!X:Z; ...READ MORE

answered Dec 19, 2022 in Others by narikkadan
• 63,600 points
1,628 views
0 votes
1 answer

Calculate Birthdate from an age using y,m,d in Excel

Hello, yes u can find your birthdate using ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,690 points
4,153 views
0 votes
1 answer

Calculate Birthdate from an age using y,m,d in Excel

Hi To Calculate the date, we can ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,690 points
2,021 views
0 votes
0 answers

Convert Rows to Columns with values in Excel using custom format

1 I having a Excel sheet with 1 ...READ MORE

Feb 17, 2022 in Others by Edureka
• 13,690 points
998 views
0 votes
1 answer

IF - ELSE IF - ELSE Structure in Excel

In this case, you can use nested ...READ MORE

answered Feb 18, 2022 in Others by gaurav
• 23,260 points
3,734 views
0 votes
1 answer

Transpose column on one sheet to row on another, with linking

select A1:J1 in worksheet B and enter: =TRANSPOSE('worksheet ...READ MORE

answered Sep 25, 2022 in Others by narikkadan
• 63,600 points
1,408 views
0 votes
1 answer

In a excel formula I need to create a list of names on one sheet based upon criteria/data of another sheet

The final formula is: =IF(ROWS($H$3:H3)<=$I$1,INDEX(Personnel! ...READ MORE

answered Nov 25, 2022 in Others by narikkadan
• 63,600 points
1,133 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP