vlookup from Right to Left

[hsandeep]

Sheet2
Lookup_value=Z4 (in Sheet2)
Table_array: Sheet1 A4:D9999

I want to get cell A & matching cell is D. What should be the vlookup formula? By using Col_index_num as -3 I am not getting the answer.

 

[Aladin Akyurek]

In both cases #N/A

What do we get with:

=LOOKUP(9.99999999999999E+307,
    SEARCH('[PIL HR DATABASE - CURRENT PIL]PIL'!$JK$4:$JK$141,AN2),
      '[PIL HR DATABASE - CURRENT PIL]PIL'!$A$4:$A$141)

 

[hsandeep]

It picked up A135 from PIL HR DATABASE - CURRENT PIL & JK135 was the 'last cell with blank' in JK4:JK141. I.e. JK136:JK141 were NOT blank & having some values in it.

 

[Aladin Akyurek]

It picked up A135 from PIL HR DATABASE - CURRENT PIL & JK135 was the 'last cell with blank' in JK4:JK141. I.e. JK136:JK141 were NOT blank & having some values in it.

If what is picked up does not correspond to the value in AN2, I suggest you upload your workbook to an uppropriate site like Google Docs or to the dropbox Public folder and provide the relevant link here.

 

[hsandeep]

No it does not correspond to the value in AN2. I tried to download Google doc drive on my PC but my office did not allowed me. I do not know how to upload it on Public folder. Can u please tell me.

 

[Aladin Akyurek]

No it does not correspond to the value in AN2. I tried to download Google doc drive on my PC but my office did not allowed me. I do not know how to upload it on Public folder. Can u please tell me.

You are probably not allowed to create a dropbox account either. Another option is to upload it to a free hosting site. There seem to exist enough such sites.

 

[hsandeep]

I work in office. The IT department is not allowing to upload any file. However I can email it to you if you give your email address.

 

[hsandeep]

In PIL HR DATABASE - CURRENT.xlsx
JI56=9 (punched), JJ56=blank (punched) & I am getting JK56=ABASAHEBS using formula =IFERROR((LEFT(UPPER(SUBSTITUTE(SUBSTITUTE(JJ56," ",""),".","")&SUBSTITUTE(SUBSTITUTE(DE56," ",""),".","")),JI56)),"")
[TABLE="width: 324"]
<TBODY>[TR]
[TD="align: right"]In Oct_2011.xlsx:





</SPAN>[/TD]
[TD] [/TD]
[TD]
[TABLE="width: 340"]
<TBODY>[TR]
[TD="align: right"]</SPAN>[/TD]
[TD] [/TD]
[TD]</SPAN>[/TD]
[TD]</SPAN>[/TD]
[/TR]
</TBODY><COLGROUP><COL span=2><COL><COL></COLGROUP>[/TABLE]
[/TD]
[/TR]
</TBODY><COLGROUP><COL><COL><COL></COLGROUP>[/TABLE]
AL2=9 (same punched), AM2=blank (same punched), I am getting AN2=ABASAHEBS using formula =IFERROR((LEFT(UPPER(SUBSTITUTE(SUBSTITUTE(AM2," ",""),".","")&SUBSTITUTE(SUBSTITUTE(D2," ",""),".","")),AL2)),"")
Now using your formula in AO2==LOOKUP(9.99999999999999E+307,SEARCH('[PIL HR DATABASE - CURRENT PIL]PIL'!$JK$4:$JK$141,AN2),'[PIL HR DATABASE - CURRENT PIL]PIL'!$A$4:$A$141) I should have got A56 but getting A131?