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

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:

=VLOOKUP(K5,VLOOKUP(MATCH("orange",A2:F2,0),P:Q,2,FALSE)&":H",2,FALSE)

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

=vlookup(6,"E:H",2,false)

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?

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!