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]

I'd still use INDEX/MATCH

=INDEX('[PIL HR DATABASE - CURRENT PIL]PIL'!$JK$4:$JK$141,MATCH(AN2,'[PIL HR DATABASE - CURRENT PIL]PIL'!$A$4:$A$141,0))
But got #N/A!!! whereas vlookup was giving CORRECT answer at this cell.[/QUOTE]

=VLOOKUP(AN2,CHOOSE({1,2},'[PIL HR DATABASE - CURRENT PIL]PIL'!$JK$4:$JK$141,'[PIL HR DATABASE - CURRENT PIL]PIL'!$A$4:$A$141),2,0)

=INDEX('[PIL HR DATABASE - CURRENT PIL]PIL'!$A$4:$A$141,MATCH(AN2,'[PIL HR DATABASE - CURRENT PIL]PIL'!$JK$4:$JK$141,0))

are necessarily equivalent.

 

[Peter_SSs]

=INDEX('[PIL HR DATABASE - CURRENT PIL]PIL'!$JK$4:$JK$141,MATCH(AN2,'[PIL HR DATABASE - CURRENT PIL]PIL'!$A$4:$A$141,0))
But got #N/A!!! whereas vlookup was giving CORRECT answer at this cell.

That's because you have your columns the wrong way around. Try

=INDEX('[PIL HR DATABASE - CURRENT PIL]PIL'!$A$4:$A$141,MATCH(AN2,'[PIL HR DATABASE - CURRENT PIL]PIL'!$JK$4:$JK$141,0))

 

[Aladin Akyurek]

Peter, I have copied & pasted the exact formula which I am using now i.e. yours. It is giving #N/A. What else can be the reasons & how to get the answer?
=VLOOKUP(AN2,CHOOSE({1,2},'[PIL HR DATABASE - CURRENT PIL]PIL'!$JK$4:$JK$141,'[PIL HR DATABASE - CURRENT PIL]PIL'!$A$4:$A$141),2,0)

I'd still use INDEX/MATCH

=INDEX('[PIL HR DATABASE - CURRENT PIL]PIL'!$JK$4:$JK$141,MATCH(AN2,'[PIL HR DATABASE - CURRENT PIL]PIL'!$A$4:$A$141,0))
But got #N/A!!! whereas vlookup was giving CORRECT answer at this cell.[/QUOTE]

=VLOOKUP(AN2,CHOOSE({1,2},'[PIL HR DATABASE - CURRENT PIL]PIL'!$JK$4:$JK$141,'[PIL HR DATABASE - CURRENT PIL]PIL'!$A$4:$A$141),2,0)

=INDEX('[PIL HR DATABASE - CURRENT PIL]PIL'!$A$4:$A$141,MATCH(AN2,'[PIL HR DATABASE - CURRENT PIL]PIL'!$JK$4:$JK$141,0))

are equivalent.

 

[hsandeep]

=INDEX('[PIL HR DATABASE - CURRENT PIL]PIL'!$A$4:$A$141,MATCH(AN2,'[PIL HR DATABASE - CURRENT PIL]PIL'!$JK$4:$JK$141,0))

are equivalent.[/QUOTE]

I used the above INDEX.... formula. But the problem which occurs is slightly different in manner. I'll try to explain, please bear with me.
I am using this INDEX formula in workbook2 (in column AO) to get a column's cell A from workbook1 provided Column AN (of workbook2) MATCHES EXACTLY with Column JK (of workbook1). I am getting AN & JK using formulas (which contains IFERROR, LEFT, UPPER & SUBSTITUTE).
Incident 1: AN2=ABASAHEBS & JK56=ABASAHEBS & I got A56 which is CORRECT.
Incident2: AN2=ABASAHEB & JK56=ABASAHEB but I am NOT ABLE to get A56? Instead I am getting #N/A How to get A56 here. What mistake could I be doing?

 

[Aladin Akyurek]

=INDEX('[PIL HR DATABASE - CURRENT PIL]PIL'!$A$4:$A$141,MATCH(AN2,'[PIL HR DATABASE - CURRENT PIL]PIL'!$JK$4:$JK$141,0))

are equivalent.

I used the above INDEX.... formula. But the problem which occurs is slightly different in manner. I'll try to explain, please bear with me.
I am using this INDEX formula in workbook2 (in column AO) to get a column's cell A from workbook1 provided Column AN (of workbook2) MATCHES EXACTLY with Column JK (of workbook1). I am getting AN & JK using formulas (which contains IFERROR, LEFT, UPPER & SUBSTITUTE).
Incident 1: AN2=ABASAHEBS & JK56=ABASAHEBS & I got A56 which is CORRECT.
Incident2: AN2=ABASAHEB & JK56=ABASAHEB but I am NOT ABLE to get A56? Instead I am getting #N/A How to get A56 here. What mistake could I be doing?

So it's not a question of Index/Match vs VLOOKUP with a table constructed using Choose, right?

If so, do one of...

=INDEX('[PIL HR DATABASE - CURRENT PIL]PIL'!$A$4:$A$141,MATCH(TRIM(AN2),'[PIL HR DATABASE - CURRENT PIL]PIL'!$JK$4:$JK$141,0))

=INDEX('[PIL HR DATABASE - CURRENT PIL]PIL'!$A$4:$A$141,MATCH("*"&AN2&"*",'[PIL HR DATABASE - CURRENT PIL]PIL'!$JK$4:$JK$141,0))

succeed also in the abnormal case?

 

[hsandeep]

I used last INDEX (WITHOUT TRIM)
Incident 1: AN2=ABASAHEB & JK56=ABASAHEB & I got A56 which is CORRECT.
Incident2: AN2=ABASAHEBS & JK56=ABASAHEBS but I am NOT ABLE to get A56? Instead I am getting #N/A

Here also I am getting AN & JK using formulas (which contains IFERROR, LEFT, UPPER & SUBSTITUTE).

 

[Aladin Akyurek]

I used last INDEX (WITHOUT TRIM)
Incident 1: AN2=ABASAHEB & JK56=ABASAHEB & I got A56 which is CORRECT.
Incident2: AN2=ABASAHEBS & JK56=ABASAHEBS but I am NOT ABLE to get A56? Instead I am getting #N/A

Here also I am getting AN & JK using formulas (which contains IFERROR, LEFT, UPPER & SUBSTITUTE).

I'm not sure whether you have answered my question: What do the two modified formulas yield?

 

[hsandeep]

I'm not sure whether you have answered my question: What do the two modified formulas yield?

Yes used your both formulas.
In both formula usage, the result was:
Incident 1: AN2=ABASAHEB & JK56=ABASAHEB & I got A56 which is CORRECT.
Incident2: AN2=ABASAHEBS & JK56=ABASAHEBS but I am NOT ABLE to get A56? Instead I am getting #N/A

 

[Aladin Akyurek]

Yes used your both formulas.
In both formula usage, the result was:
Incident 1: AN2=ABASAHEB & JK56=ABASAHEB & I got A56 which is CORRECT.
Incident2: AN2=ABASAHEBS & JK56=ABASAHEBS but I am NOT ABLE to get A56? Instead I am getting #N/A

What is the result of:

=INDEX('[PIL HR DATABASE - CURRENT PIL]PIL'!$A$4:$A$141,MATCH(TRIM(AN2),'[PIL HR DATABASE - CURRENT PIL]PIL'!$JK$4:$JK$141,0))

reagrding your "second incident"?

What is the result of:

=INDEX('[PIL HR DATABASE - CURRENT PIL]PIL'!$A$4:$A$141,MATCH("*"&AN2&"*",'[PIL HR DATABASE - CURRENT PIL]PIL'!$JK$4:$JK$141,0))

regarding your "second incident"?

 

[hsandeep]

In both cases #N/A