Offset_name range_vlookup_formula

[sachar]

Dear troubleshooter,

With reference to the attachment, do you correct me, where I am wrong?

 

[NARAYANK991]

Hi ,

Your VLOOKUP formula is :

=VLOOKUP(B1,istneeraj,MATCH(B2,istneeraj_1,0)+1,FALSE)

Here , the segment : MATCH(B2,istneeraj_1,0)+1 will return a number between 2 and 5.

Thus , the range istneeraj has to be at least 5 columns wide.

If you redefine the named range istneeraj as follows :

=OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A)-1,5)

where the change is highlighted , then your formula will work correctly.

Narayan

 

[sachar]

Dear troubleshooter,

With reference to the attachment, do you correct me, where I am wrong?

 

[sachar]

Dear Narayan,

Thanks for your quick answer, but, when I replace the 1 in 5, it is starting to show the value also in “choose name”row.

 

[NARAYANK991]

Hi ,

You need to change your dropdown source in Data Validation to :

=INDEX(istneeraj,0,1)

Narayan

 

[sachar]

Dear Naryan,

Sorry to bother you again, forwarding one more file, which I received from Mr. Canada, although, did not apply INDEX formula for Data Validation.

 

[NARAYANK991]

Hi ,

So what is your question ?

Narayan

 

[sachar]

Hi,

When I did the same exercise on the same file, and how is getting the correct answer without using the INDEX formula in Data Validation?

 

[NARAYANK991]

Hi ,

What is needed is a way to reference the column of names ; this can be done in several ways.

Narayan

 

[sachar]

Dear sir,

Ok & thanks.