Referring to a certain cell; but its not on the same row each time

[Rutger Veerman]

Hey everyone,

I'm having a bit of a struggle here, and I'm not sure if this is even possible...

Let's say I have around 300 rows in my Excel sheets, in which i want to load data from other files. I want the specific data from a year and a (for example) costumer. In this example (see attached image) I want the data from Costumer Echo, for the year 2018. 

In the left image I would have to refer to cell "E6", and in the right image that would be "F9". I don't want to do this by hand, because that would take me ages to do for 300 rows, plus it has to be updated frequently.

Is there any possible way for Excel to search for a certain value in a row/column, so it would ALWAYS refer to costumer Echo for the year 2018?

Thanks you loads in advance!

Kind regards,

Rutger

[#2 · Accepted solution]

Hi,

Sorry, but your request isn't clear enough!

But I'll give you a solution (I hope it's what you're looking for).

You can use INDEX & MATCH combination to find the intersection point in a table based on two criteria.

In the example below, the intersection point of the Customer1 row and the Year 2018 column is cell E2.

Try to change the Customer name and the Year and see how it works.

Please find that in the attached file.

Hope that helps

[#3]

This comes REALLY close to what I'm looking for. Thanks alot Haytham! This will help me alot further!

Kind regards,

Rutger

[#4]

Hello Haytham,

I've been experimenting with the formula for some time now, but i can't seem to get it right. See the attached image.

I think I copied your example correctly. Do I need a newer version of Excel maybe?

Thank you in advance!

Kind regards,

Rutger

[#5]

Hi,

You don't have to a new version of Excel!

The formula should work in any version.

As the attached image, I don't see any typo or something wrong in the formula!

Please make sure that the text (no of shares) is completed in cell J7 and doesn't have any leading spaces.

Same thing with the texts in column D.

[#6]

Hi Haytham,

Thanks for your help, but it still won't work. I don't know why it won't. I'm calling in some local Excel expert. 

Thanks again!

Kinds regard,

Rutger