Nagesh (Compliance officer) (39 Points)
25 August 2016| 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 |
satya
(Executive Excise)
(1839 Points)
Replied 09 September 2016
Dear Nagesh,
general Vlookup function will get values after matching your querry, otherwise it will show "#N/A".
Barb
(MBA)
(36 Points)
Replied 12 September 2016
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
Prodip Das
(xx)
(427 Points)
Replied 20 September 2016
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.
Prodip Das
(xx)
(427 Points)
Replied 20 September 2016
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.
Excel Mastery Program
CAclubindia
