• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

V Lookup

illanes

New Member
Hi, I wanted to ask a question that probably many have asked before, and I'm hoping there's an answer. When one copies a vlookup across columns, is there a way to do this and manitiant the lookup value cell but change the Col_index_Num consecutively (eg. lookup value $A$2, table array remains fixed, clomun moves 2,3,4,5,6 etc. ) thanks in advance.
 
set the table array by using a named range. As for the column, you will need to have a row of the column numbers. That way, you can change the reference as you move across the columns.


Assume you have a table called "datatable" from D3 to H12, so the table has 10 columns.

From B3 to B12, you have the numbers 1-10


Let's futher say you want to vlookup results to be in A3 to A12.


The lookup in A3 would be =VLOOKUP($A$2,datatable,B3,FALSE)

As you drag the formula across to A12, the column arguement will change from B3 to B4 to B5, etc.


Good luck.
 
Thanks Sachin!

One think I neglected to mention: The lookup valueI would need to remain fixed horizontally, but not vertically. In other the llokup value should move vertically like like this: A2, B2, C2 etc..
 
Hi, illanes!

The $ symbol fixes either column ($A1), row (A$1) or cell ($A$1).

So if your look-up value should remain fixed horizontally (row fixed), in Sachin's post it must be: =VLOOKUP($A2,...)

Regards!
 
ow can i match them through vlookup


Value 1 : A B C 123


Value 2 : C B A 123


This is just an example, I am using ARABIC letters instead of English letters and they are reversed due to conversion through ORACLE system.

Now i have to match them through VLOOKUP please advice
 
If you just need the column_index number to change, use the column function ie:

=VLOOKUP($A2,DataTable,COLUMN(B$1),FALSE)


This would start off returning the 2nd column of the DataTable, but if you copy it one cell to the right, formula becomes:

=VLOOKUP($A2,DataTable,COLUMN(C$1),FALSE)


and will now return the 2rd column of the DataTable.
 
No i need to match CAR REGISTRATION NUMBERS in 2 coloumn these are around 3300 values

the problems is that the alphabet sequence is change in one sheet for Eg


Sheet 1 : Plate no is E B D 1122

Sheet 2 : Plate no is D B E 1122


The number sequence is same how can i match these values:
 
skeetahs1,


My post was in response to the original author's post. It appears you are hijacking this thread with a question of your own. In the future, please start a new thread so that questions/answers do no get mixed up.


To answer your question, formula could be something like:

=VLOOKUP(MID(A2,5,1)&" "&MID(A2,3,1)&" "&LEFT(A2,1)&" "&RIGHT(A2,4),Sheet2!A:E,5,FALSE)
 
@illanes

Hi!

Please let us know if you could solve your issue or further assitance is required. Thank you.

Regards!
 
@ Luke M

Extremely Sorry for Hijacking this thread, this is my first visit to this awesome site, and was not aware of its customs ;)

Any way I got my answer thanks Luke M for ur Help.


Regards
 
Back
Top