VLOOKUP is not returning required value because column index number changes

[Sean15]

Hello:

I am using VLOOKUP to pull data from worksheet A into worksheet B. Sometimes the column index number in worksheet A charges because columns are added or deleted and when this happens, Excel is returning the wrong value unless I update the VLOOKUP formula in worksheet B. Is there a formula I can use in worksheet B to pull required data no matter what the column index number in worksheet A?

Sean

 

[AhoyNC]

If you have column headers you could use the MATCH function to find the column.

Excel Workbook
	A	B	C	D	E	F
1	ID	Data1	Data2	Data3
2	ID-1	1	2	3		5
3	ID-2	4	5	6
4	ID-3	7	8	9
5	ID-4	10	11	12
Sheet

 

[Sean15]

AhoyNC

Just tested your formula.

Thank you very much.

Regards,

Sean

 

[Jonmo1]

You can also try using INDEX/MATCH instead of VLOOKUP

=INDEX(C$2:C$5,MATCH("ID-2",$A$2:$A$5,0))

Now when a column is inserted/deleted, the C$2:C$5 reference will update automatically.

 

[Sean15]

Thank you very much Jonmo1

Regards,

Sean

 

[Jonmo1]

You're welcome.