Index(a column with a header name XXX), then match a value

lets say i have the following sheet1 with the following values
A B C D E
1 Code English Science Music Sport
2 aaa 23 56 32 89
3 bbb 33 66 42 99
4 ccc 13 16 12 19
5 ddd 3 6 62 79

now on sheet2 i want to return the values in english and music colmn with formula without referring to a specific column on sheet1
because the heading english or music can change position

A B C D E
1 code english music
2 ccc 13 12
3 bbb 33 42
4 aaa 23 32


the formular i wanted to write is index/match but i dont know how to frame it
something like

sheet2 formula in cell B4 will be

= look at cell B1 , get the value there , then on sheet1 look at range(A1:E5)
find the column that contains the value equal to the value on sheet2(B1)
then return the english score of code "aaa"

any idea please?

Hi,

You are correct. This kind of formula is INDEX/MATCH.

Give this a try:

Please Login or Register  to view this content.

Hope this is helpful.

Cheers

Both reply from Southward and Tony worked perfectly , you all made my day , thanks and keep it up

Glad I could be of some assistance.

Cheers