Dynamic formula to check column and apply

[harinsh]

Hi Team,

I have data and everytime when I extract columns re-arranged automatically however, header names never change, hence, whenever I update formula the reference of column will change everytime.

Is there any way to check column header if that's true then execute the rest of formula?? So, that it will pick the right data as input.

 

[Jonmo1]

You can likely use Index and Match for that.
But we can't really offer specifics without seeing your actual formula.

 

[harinsh]

Ok, here is the sample data and formula using...assume data start from A1 cell to C14 cell

Serial Number	Name	Age
2948	A	40
3685	B	41
1020	C	43
3737	D	40
1724	E	39
4850	F	35
2805	G	46
1950	H	46
1108	I	36
1928	J	33
1597	K	44
3166	L	34
1052	M	42

<tbody>
</tbody>

Serial Number	Formula
3737	D =VLOOKUP(E3,$A$1:$C$14,2,0)
1724	E

<tbody>
</tbody>

If tomorrow column A re-align to column b...formula should work

 

[Jonmo1]

Which version of Excel are you using?

If XL2007+, convert that range to a Table by highlighting the range, On the Insert Tab, click Table.
Make sure to check "My Table has headers"

Then you can use
=INDEX(Table1[Name],MATCH(E3,Table1[Serial Number],0))

 

[harinsh]

Thanks for your quick response, May I know what is exactly "Serial Number" here ...is this second table where I'm taking data from column number ?

 

[Jonmo1]

Serial Number is the Header, you also have headers Name and Age

 

[harinsh]

Perfect, thanks for your time

 

[Jonmo1]

You're welcome.

 

[harinsh]

Hi Jonmo1,

I still see formula only look at column based on column...if column changes formula still referring the old column only. Any work around for this.