lookup cell and report back the best matching result

Pulling on your expertise, I am learning as I go, no one told me Excel was so powerful...

Ok, so I use Vlookup daily and never an issue HOWEVER

I need to use Vlookup or match or another condition or group to pull data in as a best match.

I have attached a spreadsheet.

if anyone can point me in the right direction I will be very greatful.

so I have a cell, I want to search for the closest matching cell in a collumn and report back an adjacent cell

(exactly the same as Vlookup but with closest match rather than perfect match.

Thank you so much for looking at this.

What if there are two potential matches that cannot be separated (i.e. one is no better than the other)?

OK. Let's put it another way! Please could you provide a copy of your sample spreadsheet with your expected outcomes filled in manually and tell us why you think they are the best fit?

OK - that's clearer now, thanks. Going to have to ponder this one! There are lots of real gurus around here who will no doubt see a solution faster than me.

Hi jaamba123 and welcome to the forum.

If I have interpreted correctly this does what the instructions state.

It requires two helper columns to establish the year ranges. In sheet 1 enter this in cell C2 (done in the attached). Then fill down and across column D.

Formula:

Please Login or Register  to view this content.

Then in B2 array enter this formula and fill down. If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

Formula:

Please Login or Register  to view this content.

Originally Posted by jaamba123

Thank you Flame Retired for your help and I must say you have done exactly what I asked and I am so grateful, I have spent days trying to understand and modify this so I can run it in a sheet with 16,000 columns not just the 45 in this sheet but alas failed miserably ........
James

16000 rows

That changes everything.

I would guess it is taking a very long time to calculate if it doesn't crash Excel.

If you are not aware of it array formulas are resource hungry. That creates a challenge.

With that in mind I will attempt another formula solution. If I can it will likely require several helper columns.

In the meantime I managed to shorten the array formula to this

Formula:

Please Login or Register  to view this content.

This might be of some use to you. If you make a data table out of the data on Sheet2, you can substitute the names of the columns into the formula given by FlameRetired and you should be able to handle all the rows without further amendment.
Here is FlameRetired's formula using Table references (the ranges automatically increase as the table increases). You will see that all reference to Sheet2 has been replace with a reference to Table1 with the appropriate column header from the table. This is still an ARRAY formula so enter with Ctrl+Shift+Enter.

Formula:

Please Login or Register  to view this content.

Why not just add filters to the data on Sheet2 and use the filters to extract the data that you want to see? You can select date ranges, models etc and matching data will be presented to you without having to use formulae to extract the data.
Example:
Capture.JPG

I think newdeoverman's idea of filtering is the way to go.
Just to offer another option - if you want to extract data to another sheet using filters then you might consider using Advanced Filter with multiple criteria.

Here is a link to Advanced Filter tutorials on YouTube

Here are a couple of links to web tutorials:
Excel-Easy: Advanced Filter
Contextures: Introduction to Advanced Filter

Here is a copy of your example workbook with an Advanced Filter used. I changed the name of the worksheets to make the example filter more clear.
Instructions on how to do this are included.