Match a value to column header in another worksheet and retrieve last used cell data

[WednesdayC]

Hi All
Please can someone help me as I really stuck with this.
I need to match several values to another worksheet, where these values will be the column headers and retrieve the last entry in that column.
So for instance - say I am looking for "CHECK 4" on 1 sheet and this will be the header of one of the columns in another sheet - (the position of the column header will change, but it will always be a column header and assuming it is row 1 of column D for now) then I need to retrieve the last entry in column D on to my other worksheet.

I am happy with either a formula or VBA. Any help would be appreciated.

Many thanks in advance

Wednesday

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[WednesdayC]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Hi Fluff

Thank you - I will do so - it is some time since I have posted.
I am using Excel 2016 (Excel for Mac)

Thanks
Wednesday

 

[Fluff]

Thanks for that, how about

=LOOKUP(2,1/(INDEX(Sheet1!A2:Z10000,,MATCH(F2,Sheet1!A1:Z1,0))<>""),INDEX(Sheet1!A2:Z10000,,MATCH(F2,Sheet1!A1:Z1,0)))

Change F2 to the cell with "Check 4"

 

[WednesdayC]

=LOOKUP(2,1/(INDEX(Sheet1!A2:Z10000,,MATCH(F2,Sheet1!A1:Z1,0))<>""),INDEX(Sheet1!A2:Z10000,,MATCH(F2,Sheet1!A1:Z1,0)))

Hi Fluff

Wow - it works a treat! Thank you so much.
I have spent ages trying to come up with a solution myself.

Regards
Wednesday

 

[Fluff]

You're welcome & thanks for the feedback.