| need to Vlook for Docuement for WHT and the querry is I have to get WHT value for only first occurrence and next occurences to get blank or any text like " duplicate" |
| Sheet 1 | Sheet 2 | |||
| Doc No. | WHT | Doc No. | WHT | |
| 7630001567 | 58.00 | 7630001567 | ||
| 7630001570 | 407.00 | 7630001570 | ||
| 7630001581 | 48.00 | 7630001581 | ||
| 7630001584 | 910.00 | 7630001584 | ||
| 7630001594 | 256.00 | 7630001594 | ||
| 7630001595 | 103.00 | 7630001570 | ||
| 7630001599 | 138.00 | 7630001581 | ||
| 7630001600 | 378.00 | 7630001584 | ||
| 7630001606 | 175.00 | |||
| 7630001609 | 279.00 |
Dear Nagesh,
general Vlookup function will get values after matching your querry, otherwise it will show "#N/A".
take first half of course for free covers vlookup and many more will help you solve this issue https://courses.corporatefinanceinstitute.com/courses/excel-crash-course-for-finance
Nagesh, VLOOKUP function can perfectly work for you. However while selecting the range of data you need to be adopt some trick.
Suppose you have the data in COL A2: B11. For your understaing I am showing the example in same sheet COL D2:E11. In your case you do not want fetch WHT value in case it repeats. So the formula would be here in E2 cell:
=IFERROR(VLOOKUP (D2,$A2:$B$11,2),"")
Why $A2 ? It will skip the beginging row one by one as you copy the formula in next rows
Why $B$11? It will freez the data range upto B11.
Why IFERROR ? If you use IFEEROR and "" it will replaced the value #NA with blank using "" sign
Please refer the attached file. Hope it clarifies your query.
Friends the combination of above function still have some loop holes and may not perform well. There we need to restructure the function as attached herewith the file:
In cell E2 the function to be keyed in as =IFERROR(IF(IFERROR(VLOOKUP(D2,$D$1:D1,1,0),"")=D2,"",VLOOKUP(D2,$A$2:$B$11,2,0)),"")
Now it will work perfectly. Request you to go through the attached file for your understanding.
CAclubindia
