dear sir
attahced pls find file, which should match a2,b2 from range n2 to q100 which match
column n and q
=INDEX($N$3:$Q$100,MATCH(1,($A$2=$N$2:$N$100)*($B$2=$Q$2:$Q$100),0))
the formula is wrong .
pls help
tks'
tom
dear sir
attahced pls find file, which should match a2,b2 from range n2 to q100 which match
column n and q
=INDEX($N$3:$Q$100,MATCH(1,($A$2=$N$2:$N$100)*($B$2=$Q$2:$Q$100),0))
the formula is wrong .
pls help
tks'
tom
It would not work dear due to syntax error.
Why don't try in D2=IF(ROW($D1:D1)<=$C$3,VLOOKUP($A$2,OFFSET($N$1,MATCH($A$2&$B$2,INDEX($N$2:$N$15&$Q$2:$Q$15,),0)+ROWS($C$2:C2)-ROW($C$1),0,$C$3,4),COLUMNS($D1:D1),FALSE),"")
It will understand and make dynamic ranges for Vlookup.
XL file is attached for your help.
If I helped, Don't forget to add reputation (click on the little star ★ at bottom of this post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
Hi,
Try the following formula in D2:
{=IFERROR(INDEX(N$1:N$15,SMALL(IF(($N$1:$N$15=$A$2)*($Q$1:$Q$15=$B$2),ROW($N$1:$N$15)),ROW(A1))),"")}
array entered, needs to be confirmed by pressing CTRL+SHIFT+ENTER
drag this across and down.
See the attached file.
hi
when i fill d7 with forumula =IFERROR(INDEX(N$1:N$15,SMALL(IF(($N$1:$N$15=$A$3)*($Q$1:$Q$15=$B$3),ROW($N$1:$N$15)),ROW(A1))),"")
it dont work how to fill the range d7 to g9
attached pls find file
thanks
tom
Hi,
The same formula works, its an array formula & you need to press CTRL+SHIFT+ENTER instead of just ENTER to confirm.
See the attached file.
Thank for your great help. It is great
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks