Multiple Lookup Value

[hardeep.kanwar]

Hello Experts


Attached is my Master Data Just an Example

Now, what i need to Update Fee Agnt Every Student Date wise in Sheet2,When I Put Vlookup In Sheet 2 The Result is Same in All Cell Match with Master Sheet

 

[Somendra Misra]

Hi,

See the file.

Regards,

 

[hardeep.kanwar]

Man , U R Actual a Excel Ninja

Let me Experiment in my Actual Sheet, Will Let u know, in case of any Discrepancy

Thanks for your valuable Solution

 

[Somendra Misra]

Sure, write back if you face any problem, otherwise we can go along with VLOOKUP & helper column also.

Regards,

 

[Khalid NGO]

Hi SM Sir, how are you?

I applied simple =INDEX(MASTER!$A$2:$D$311,ROW()-1,COLUMN(C1))
and getting the same result as yours:
=INDEX(MASTER!D$2:D$31,SMALL(IF(Sheet1!$A2=MASTER!$A$2:$A$31,ROW(MASTER!$A$2:$A$31)-ROW(MASTER!$A$2)+1),COUNTIF($A$2:$A2,$A2)))


Can you please clarify the use of SMALL and COUNTIF in above?
What will be the advantages of using these two in above case?

Array formula always confuse me

 

[Somendra Misra]

Hi Khalid,

Your formula will work for this type of output data requirement, where all the names say ABC appearing 5 times in alternate rows. But considering the situation if ABC is in row 2,3 and than on row 10, I think it will fail. Just try experimenting with this.

Where as in my formula: in SMALL function is used to get row number by comparing name and assigning a unique no. than COUNTIF will give 1,2,3 and so on as we drag the formula down as the range in it is expanding in nature so we will get 1st Small, than 2nd and 3rd and so on.

Regards,

 

[Khalid NGO]

Wow....
I tried experimenting the sheets and found your formula giving accurate result.
i moved data of row# 16 to row#10 (for mixing up names)
I have highlighted this in red.

Thank you SM Sir for the explanation.

 

[hardeep.kanwar]

Sure, write back if you face any problem, otherwise we can go along with VLOOKUP & helper column also.

Regards,

Thank U Sir, Its Works Perfectly ,Thanks a Lot