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.

 

[VoG]

Try


=INDEX(Sheet1!D4:D9999,MATCH(Z4,Sheet1!A4:A9999,0))

 

[Vidar]

Deleted
(same solution)

 

[Peter_SSs]

I'd use the INDEX/MATCH too, but for what it's worth you can do it with VLOOKUP as well.

=VLOOKUP(Z4,CHOOSE({1,2},Sheet1!$D$4:D$9999,Sheet1!$A$4:A$9999),2,0)

Some explanation here if you want it.

 

[hsandeep]

I am getting #N/A with abv formula. A4, D4 & Z4 are formula generated values.

 

[Peter_SSs]

I am getting #N/A with abv formula. A4, D4 & Z4 are formula generated values.

Typically that would imply either ..

- that the Z4 value does not occur in D4:D99 of Sheet1 (could be a rounding issue), or

- that in Sheet1 there is a #N/A value in column A corresponding to the Z4 value in column D of Sheet1

Are you saying that for exactly the same data, the INDEX/MATCH formula is giving the correct result and the VLOOKUP is not?

 

[hsandeep]

Peter,
Yr formula is giving correct answer.

 

[hsandeep]

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)

 

[Peter_SSs]

What else can be the reasons & how to get the answer?

That formula works for me but, as I said before ..

I'd use the INDEX/MATCH

The reasons the formula would give #N/A are the same as I gave before:

.. either ..

- that the AN2 value does not occur in JK4:JK141 of the 'PIL' sheet (could be a rounding issue), or

- that in the 'PIL' sheet there is a #N/A value in column A corresponding to the AN2 value in column JK

I don't know what your data is like but another reason "that the AN2 value does not occur in JK4:JK141 of the 'PIL' sheet" could be that one is numerical and one is text.

I'd still use INDEX/MATCH

 

[hsandeep]

I'd still use INDEX/MATCH [/QUOTE]

=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.