• 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.

Edited vlookup help

T

tanwar

New Member
my formula is below:

=VLOOKUP($B$5&"1",$D$5:$F$30,2,FALSE)

I HAVE WRITTEN FORMULA IN SHEET#2 AT A1

BUT I COPY THIS FORMULA AT A2, VALUE OF ($B$5&"1") IS NOT CHANGED, I WANT TO CHANGE NEXT

AT A2 ($B$5&"2") AT A3($B$5&"3")ONWARDS.....


PLEASE HELP ME
 
If you want the concatenated text string to increment, formula would be:

=VLOOKUP($B$5&ROW(A1),$D$5:$F$30,2,FALSE)


@bobhc

This was your chance to beat me. =)
 
Tanwar

The ROW function is just returning a number, not a value. In the first cell, formula will be:

=VLOOKUP($B$5&ROW(A1),$D$5:$F$30,2,FALSE)

The ROW function evaluates to 1, creating:

=VLOOKUP($B$5&1,$D$5:$F$30,2,FALSE)

which is what you wanted.


When you copy the formula down, the cell reference within the row function will change like so:

=VLOOKUP($B$5&ROW(A21),$D$5:$F$30,2,FALSE)

which evaluates to:

=VLOOKUP($B$5&2,$D$5:$F$30,2,FALSE)

which is what you want.


Please correct if any of the above statements are incorrect, thanks.
 
Apologies tanwar, I'm not able to access uploaded workbooks from my location. I was hoping someone else would be able to come along and see what the issue was, or you can try again showing us what formula you have currently and what you are wanting it to change into.
 
Hi Tanwar,


Luke's solution was perfect for this situation..


Just use..

Code: 
=VLOOKUP(B$5&ROW(A1),'Data Sheet'!$C$5:$L$18,7,FALSE) and drag to the below..


https://dl.dropbox.com/u/78831150/Excel/VLOOPUP%20SOLUTION%28Edited%20vlookup%20help%20-%20tanwar%29.xls


BTW.. Luke have some better solution for the same situation.. you can use this if you don't want to add extra step at sheet 1.. [code]=B5&COUNTIF($B$5:$B5,B5)
:)


I have also mentioned the same in attached file..


{=INDEX('Data Sheet'!$I$4:$I$18,SMALL(IF('Data Sheet'!$B$4:$B$18='Summery '!$C$5,ROW('Data Sheet'!$B$4:$B$18)-3),ROW(A1)))}[/code]


For more detail visit..

http://chandoo.org/wp/2011/11/18/formula-forensics-003/


Regards,

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