• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

in Vlookup / match - how to update contents of next row of same lookup value

S

shreevvairam

New Member
Hi,


I have two tables. I want to update contents from one table to another table. I have tried to use vlookup, match, index, two way match/index, helper column But it update only contents in first row and skip second or any successive row even if it is in different column.I am aware that vlookp update contents of only first match. But I want to update contents of second as well as any successive match as it is of differnt date and different colum.


I have attached sample file to understand this problem. I am sorry I unable to explain properly. I have tried to use below formulas but no result.


IF(ISNA(HLOOKUP(F$7,$F$135:$AJ$247,MATCH($B65,$A$136:$A$247,0)+1,FALSE)),0,HLOOKUP(F$7,$F$135:$AJ$247,MATCH($B65,$A$136:$A$247,0)+1,FALSE))


=IF(ISNA(VLOOKUP($B4,$B$20:D$29,3,0)),0,VLOOKUP($B4,$B$20:D$29,3,0))


I know little about formula and function but not used to VBA or macros


Result File

Emp ID Name 1-Oct 2-Oct 3-Oct 4-Oct 5-Oct 6-Oct 7-Oct

1 A L 0 0 0 0 0 0

2 B 0 0 0 0 0 0 0

3 C 0 0 0 0 0 0 0

4 D L L 0 0 0 0 0

5 E 0 0 0 0 0 0 0

6 F 0 0 0 0 0 0 0

7 G 0 0 0 0 0 0 0

8 H 0 0 L 0 0 0 0

9 K 0 0 0 0 0 0 0

10 L 0 0 0 0 0 0 0


Data File

Emp ID Name 1-Oct 2-Oct 3-Oct 4-Oct 5-Oct 6-Oct 7-Oct

1 A L 0 0 0 0 0 0

1 A 0 0 L 0 0 0 0

1 A 0 0 0 0 0 L 0

4 D L L 0 0 0 0 0

4 D 0 0 0 L 0 0 0

4 D 0 0 0 0 0 L 0

8 H 0 0 L 0 0 0 0

8 H 0 0 0 0 0 0 L

9 K 0 0 0 0 0 0 0

10 L 0 0 0 0 0 0 0


How to attcah file in our post. Can anyone guide me?

Thanking in advance


Regards


Shree
 
Hi,


To upload the workbook please try the below links:


http://www.2shared.com/

http://www.speedyshare.com/


Thanks,

Suresh Kumar S
 
Hi Vairam ,


I do not know whether the version of Excel you have will allow this function , but try using this , in D4 ; copy it across and down :


=IF(COUNTIFS(D$20:D$29,"L",$B$20:$B$29,$B4)>0,"L",0)


Narayan
 
Hi Vikram,


In case, the version of excel you are using deos not support COUNTIF, you can use the following alternative approach...


At D4 try the following:


=IFERROR(INDIRECT(CHAR(67+COLUMN(A$1))&SMALL(IF(($B$20:$B$29=$B4)*(D$20:D$29="L"),ROW($D$20:$D$29),""),ROW($A$1))),"") (CTRL+SHIFT+ENTER) not just enter...


Copy it down and across..


But I would suggest you to use Narayan's formula (in case your excel is compatible with COUNTIF) as it is very simple and easy to follow...


Cheers,

Kaushik
 
Hi Narayan,,Kaushik,


Thanks for your quick help... you are right about version as this formula (Naryan's) is working on my computer at home having advance version.. however it is not working on my office PC as it haveing 2003 version.


I will try formula given by Mr. Kaushik tomorrow on my office PC and give you confirmation.


However I am just curious to know... is their any way to use Vlookup/match function to update contents of multiple rows having same lookup value... or it is not possible at all?


Wish you Happy dussrah to you and your loved one..


Best regards


Shree
 
You must log in or register to reply here.
Back
Top