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

Name	Age	Remarks
daff	1
aaa	3
fgh	2
a	33
ggg	77
d	4
fff	88
fffdfs	90

<tbody>
</tbody>

Sheet 2:

A B C D

sl no	Name	age	rem
1	daff	12	cow
2	aaa	4	dog
3	fgh	88	sheep
4	a	6	goat
5	ggg	9	monkey
6	d	7	bull
7	fff	88	fox
8	fffdfs	5	lion

<tbody>
</tbody>

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.