Lookup values from a sheet where reference value and lookup value keeps changing column positions

[eta004]

Dear Experts,

My requirement is to lookup data corresponding to " Name" in Sheet 1 where every header is fixed in position, from Sheet2 where the reference value ("Name") as well as lookup values("rem") keeps changing postions(columns)..Due to this when using Vlookup which uses Row column reference will give me wrong results.

I tried INDEX MATCH function also but in that also I couldnt fix the row number for reference value ....Anyone kindly help as I am in bit of confusion ...and Im novice in VBA and macros.


=INDEX(Sheet2!$A$1:$D$9,MATCH(Sheet1!$A2,Sheet2!$B:$B,0),MATCH("rem",Sheet2!$1:$1,0))

In the above formaula I need help in the first MATCH function on how I can autodetect the " Sheet2!$B:$B" which keeps on changing in trends and detect the column in sheet2 which the corresponding header name "Name" .

I will simplify the scenario as below:

Sheet1:
A B C
[TABLE="width: 192"]
<tbody>[TR]
[TD="class: xl63, width: 64"]Name[/TD]
[TD="class: xl63, width: 64"]Age[/TD]
[TD="class: xl63, width: 64"]Remarks[/TD]
[/TR]
[TR]
[TD="class: xl63"]daff[/TD]
[TD="class: xl63"]1[/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]aaa[/TD]
[TD="class: xl63"]3[/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]fgh[/TD]
[TD="class: xl63"]2[/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]a[/TD]
[TD="class: xl63"]33[/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]ggg[/TD]
[TD="class: xl63"]77[/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]d[/TD]
[TD="class: xl63"]4[/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]fff[/TD]
[TD="class: xl63"]88[/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]fffdfs[/TD]
[TD="class: xl63"]90[/TD]
[TD="class: xl63"][/TD]
[/TR]
</tbody>[/TABLE]


Sheet 2:

A B C D
[TABLE="width: 256"]
<tbody>[TR]
[TD="class: xl65, width: 64"]sl no[/TD]
[TD="class: xl66, width: 64"]Name[/TD]
[TD="class: xl65, width: 64"]age[/TD]
[TD="class: xl66, width: 64"]rem[/TD]
[/TR]
[TR]
[TD="class: xl65"]1[/TD]
[TD="class: xl65"]daff[/TD]
[TD="class: xl65"]12[/TD]
[TD="class: xl65"]cow[/TD]
[/TR]
[TR]
[TD="class: xl65"]2[/TD]
[TD="class: xl65"]aaa[/TD]
[TD="class: xl65"]4[/TD]
[TD="class: xl65"]dog[/TD]
[/TR]
[TR]
[TD="class: xl65"]3[/TD]
[TD="class: xl65"]fgh[/TD]
[TD="class: xl65"]88[/TD]
[TD="class: xl65"]sheep[/TD]
[/TR]
[TR]
[TD="class: xl65"]4[/TD]
[TD="class: xl65"]a[/TD]
[TD="class: xl65"]6[/TD]
[TD="class: xl65"]goat[/TD]
[/TR]
[TR]
[TD="class: xl65"]5[/TD]
[TD="class: xl65"]ggg[/TD]
[TD="class: xl65"]9[/TD]
[TD="class: xl65"]monkey[/TD]
[/TR]
[TR]
[TD="class: xl65"]6[/TD]
[TD="class: xl65"]d[/TD]
[TD="class: xl65"]7[/TD]
[TD="class: xl65"]bull[/TD]
[/TR]
[TR]
[TD="class: xl65"]7[/TD]
[TD="class: xl65"]fff[/TD]
[TD="class: xl65"]88[/TD]
[TD="class: xl65"]fox[/TD]
[/TR]
[TR]
[TD="class: xl65"]8[/TD]
[TD="class: xl65"]fffdfs[/TD]
[TD="class: xl65"]5[/TD]
[TD="class: xl65"]lion[/TD]
[/TR]
</tbody>[/TABLE]

Any methods are welcome....Pls help me out of this ..........

 

[eta004]

Awaiting helps from any onee....................

 

[Aladin Akyurek]

What are the look up values, a name and the associated age from Sheet1?

 

[eta004]

Name and remarks

 

[eta004]

I have manged to put it this way

Dim ref As Integer









Sheets("Sheet2").Select
Range("F3").FormulaR1C1 = "=MATCH(""Name"",Sheet2!R1,0)"



ref = ActiveSheet.Cells(3, 6).Value


Sheets("Sheet1").Select
Range("E3").Select
ActiveCell.FormulaR1C1 = _
"=INDEX(Sheet2!R1C1:R9C4,MATCH(Sheet1!RC1,Sheet2!C" & ref & ",0),MATCH(""rem"",Sheet2!R1,0))"




End Sub



In this I need to avoid the "ref" value to be taken from a cell and need to directly apply to the formula.."=MATCH(""Name"",Sheet2!R1,0)"
Any ideas for that?

like ref="=MATCH(""Name"",Sheet2!R1,0)"

 

[Aladin Akyurek]

Name and remarks

In C2 of Sheet1 enter and copy down:

=INDEX(Sheet2!A:D,MATCH(A2,INDEX(Sheet2!A:D,0,MATCH("name",INDEX(Sheet2!A:D,1,0),0)),0),
    MATCH("rem",INDEX(Sheet2!A:D,1,0),0))

 

[eta004]

Awesome............worked like a charm.........Thanks a lot

 

[Aladin Akyurek]

Awesome............worked like a charm.........Thanks a lot

You are welcome.