Using Match and Vlookup to find the lookup range for a Vlookup

[locopete999]

Hi Guys,

One of my colleagues is trying to programme a macro to do a vlookup.

The problem is that the source data doesn't always have the same column headings in the same columns.

I have looked at the below:

Please Login or Register  to view this content.

This is just test data. in P:Q I have numbers 1 through 10 in P and then A,B,C etc in Q.

This mostly works, but the resulting formula is

Please Login or Register  to view this content.

This is the end foruma that I'm looking for, but I think that the " around the column range E:H is what is returning a #Value error.

Can someone look at this and advise please?

[ChemistB]

Can you upload an example workbook (Go Advanced>scroll down to "Manage Attachments")

Do the headers change or change order? Give a couple small examples of how it might look and what you are looking to return.

Sounds like something that can be done easily with INDEX and MATCH.

[locopete999]

Hi,

I dont have the actual data as I am working on this for a colleague and
they are hesitant to release a copy of company data (despite being told that
no one will have a clue what its about!)

The problem is that the columns change order every time. We cant help this
as the information is gathered from a customer.

I have attached my trial spreadsheet that I mocked up to try and solve the
problem. hopefully this will help you see where I was going in my solution.

As for Index Match, I am familiar with Match, but never had much need to use
Index, so I am not familiar with what it does, so I'll look into that!