2 sheets and two data points I need to match to bring forward a $ from one sheet to the next.

Question

I am comparing renewal premiums to determine the %age increase in terms. I have last years data in one sheet '2025' , this years data in another sheet '2026' and they are on one file (tabs) . I am entering the equation in '2025' searching for a match in column A AND column I in both to be exact. once found I need the premium from '2026' column S to be entered into sheet '2025' column U.

I have figure it out partly by using a VLOOKUP equation, but i have duplicates in column A, and it is not searching the column I to narrow it down more. =VLOOKUP(A17,'2026'!$1:$1048576,19,FALSE) so i get a duplicate # for each when they should be different.

Answer 1

To achieve your goal of matching data from two sheets based on two criteria (columns A and I) and bringing forward the premium from the '2026' sheet to the '2025' sheet, you can use a combination of the INDEX and MATCH functions instead of VLOOKUP. This approach allows you to specify multiple criteria for your lookup.

Here’s how you can set it up:

1. In the '2025' sheet, where you want to retrieve the premium, enter the following formula in column U (assuming your data starts from row 2):

      =INDEX('2026'!S:S, MATCH(1, ('2026'!A:A=A2) * ('2026'!I:I=I2), 0))
   

   • This formula uses INDEX to return the value from column S of the '2026' sheet.
   • The MATCH function looks for a row where both conditions are met: the value in column A of '2026' matches the value in column A of '2025', and the value in column I of '2026' matches the value in column I of '2025'.
   • The 1 in the MATCH function indicates that we are looking for a match of both conditions (which are multiplied together).
2. Array Formula: Since you are using an array operation, you need to enter this formula as an array formula. To do this, after typing the formula, press Ctrl + Shift + Enter instead of just Enter. Excel will add curly braces {} around the formula to indicate that it is an array formula.

This method should help you avoid duplicates and correctly match the premiums based on both criteria.

---

References:

• Excel performance: Tips for optimizing performance obstructions

Answer 2

Hi,

Share some data to work with and show the expected result. Share data in a format that can be pasted in an MS Excel file.