Quick VLOOKUP

[unknownymous]

Hi Guys,

Just wanted to ask you have any other way of doing vlook ups in a fastest way.

Example: Reference Tab

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]ID[/TD]
[TD]Name[/TD]
[TD]Subject[/TD]
[TD]Score[/TD]
[TD]Note[/TD]
[/TR]
[TR]
[TD]001[/TD]
[TD]Ben[/TD]
[TD]Math[/TD]
[TD]90[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD]002[/TD]
[TD]Ana[/TD]
[TD]Science[/TD]
[TD]97[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]003[/TD]
[TD]Sean[/TD]
[TD]Science[/TD]
[TD]85[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]004[/TD]
[TD]Millie[/TD]
[TD]Math[/TD]
[TD]87[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

LookUp File

[TABLE="width: 500"]
<tbody>[TR]
[TD]ID[/TD]
[TD]Name[/TD]
[TD]Subject[/TD]
[TD]Score[/TD]
[TD]Note[/TD]
[/TR]
[TR]
[TD]004[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]003[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]001[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]-002[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]-[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

In Lookup file, I normally use this formula in B2 "=vlookup($A:$A,Reference!$A:$G,1,0)" and in C3 "=vlookup($A:$A,Reference!$A:$G,2,0)" and will add +1 (col cel) to the right.

Question: Is there any other formula/way in which it will automatically add +1 for the col index cell?

Any help will be much appreciated as it will save me some time in working with massive data.


Thank you!

 

[Yongle]

Instead of
vlookup($A:$A,Reference!$A:$G,1,0)
Try
vlookup($A:$A,Reference!$A:$G,COLUMN(A1),0)

 

[unknownymous]

Works perfectly, thank you!

 

[Aladin Akyurek]

Let A:E of Sheet1 house the lookup table.

In B2 of Sheet2 enter, copy across, and down:

=VLOOKUP($A2,Sheet1!$A:$E,MATCH(B$1,INDEX(Sheet1!$A:$E,1,0),0),0)

where A2 = 004, etc. and B$1 = Name, Subject, and so on.