Vlookup or index/match solution

[Lauren Thomas]

Hello
Please can you help me with an Excel formula.

Please see the attached doc.

On the worksheet 'Eproductive' I wish to create a formula in column I that returns the value in column H if it matches with the values on the 'bank Statement' worksheet in column F but also only if the Shop ID in column B on the 'Eproductive' worksheet agrees to the reference on the 'Bank Statement' worksheet in column C.

As this formula would have multiple criteria I am not sure if I should use a VLOOKUP or some form of INDEX and MATCH.

I would be very grateful for some assistance.

Many thanks
Lauren

 

[Hui]

Lauren

There is nothing attached?

 

[Lauren Thomas]

so sorry, now attached!

 

[David Evans]

Take a moment to annotate exactly where/what you want "looked up" and dummy in the answer you expect. Use the comment feature of Excel in the cells you're looking to have answers. This makes it a lot easier, and therefore quicker, to find the answer you need.

Also clarify the Lookup Data section of your EPRODUCTIVE sheet - I think the lower case letters refer to Columns, but I don't get the three rows of integers - column headings are missing.

 

[Lauren Thomas]

Thank you for your time. I am sorry that I am not explaining this very clearly! (I am far from the most technical and don't fully understand all of the terminology, I am sorry!)

I want to pick up the value in column F on the 'Bank Statement' tab and return this figure in column I (BANK) on the 'Eproductive' tab if the SHOP number on the 'Eproductive' tab in column B agrees to the REFERENCE in column C on the 'Bank Statement' tab (I have highlighted red) as well as the EPROD value in column H on the 'Eproductive' tab agreeing to the VALUE in column F on the 'Bank Statement' tab (I have highlighted these purple).

For example, (Row 13 on Eproductive tab).
Column H shows 835.34 which can be found on row 52 Column F on the Bank statement tab.
As long as the number (1002) in columns B on the Eproductive tab and column C on the bank statement tab match (which they do in this example) then I want to return the value 835.34 in column I on the Eproductive tab.

Thank you so much for your patience!

 

[NARAYANK991]

Hi ,

See if this is OK.

Narayan

 

[Lauren Thomas]

This is absolutely fantastic- thank you very very much!

 

[David Evans]

This is absolutely fantastic- thank you very very much!

@NARAYANK991 is a star; however your explanation made it a lot easier to get to the correct answer efficiently.

The SUMPRODUCT() solution he provided is a favorite technique of mine also - i just like the mechanism!

As you Bank Statement is a constantly evolving listing, you might want to think about making it into an Excel Table so that you don't have to constantly amend your formulas (my preferred method) or you can use dynamic ranges which will accommodate the changing size of the range. The latter is a little more complicated and it's a technique that you see less of these days but it works well - you use OFFSET and COUNTA to automatically extend your range as you add more data ....

Hope all is well in Buckinghamshire where I spent a pleasant few years at school.

 

[Lauren Thomas]

Thank you both so much. I'm sorry I found it so difficult to explain this one - I've never really needed to do this before. I do the bank rec weekly so I carry the balances forward and start with the new data each week. There would be too many entries to manage if I were to keep adding to it.