vlookup question
How do I make it so that the # of rows over increases by 1 everytime I move over one to the right? For example, take in cell B2 I have vlookup(B1,some range,3,0) and in B3 I want vlookup(B1,some range,4,0) and in B4 I want vlookup(B1,some range,5,0), etc. How do I set up my formula so that the col index number increases as I move right?
Make the numbers cell references to cells that increase.
So say in A1-A25 I have the numbers 1,2,3,4, etc till 25. Have my vlookup(reference,range,vlookup of 1,2,3,4 etc,0) like that?
=vlookup(reference, range,A1,0)
When you copy that across it will automatically become =vlookup(reference, range,A2,0)
How do I make it so that the # of rows over increases by 1 everytime I move over one to the right? For example, take in cell B2 I have vlookup(B1,some range,3,0) and in B3 I want vlookup(B1,some range,4,0) and in B4 I want vlookup(B1,some range,5,0), etc. How do I set up my formula so that the col index number increases as I move right?
Reference the row. try the following formula for cell B1, B2, B3, B4, b5 in a blank worksheet.
=Row(A15) =Row(A16) =Row(A17) =Row(A18) =Row(A19)
Just make the vlookup a function of the that. So you just need to figure out what the offset is betweem these values.
For example in your above example B2's lookup row/col is 3, while B4's is 5. So obviously your lookup row/col is Row(Range)+1. You would change your above formula to:
B2 = vlookup(B1,some range,(Row(B2))+1,0) B4 = vlookup(B1,some range,(Row(B4))+1,0)
B2 = vlookup(B1,some range,(Row(B2)+1),0) B4 = vlookup(B1,some range,(Row(B4)+1),0)
Pariatur molestiae exercitationem aut doloremque aperiam. Iusto eveniet rem dolorum consequatur veritatis quis quae. Ut voluptatem accusantium commodi explicabo aut necessitatibus. Impedit impedit quis unde rerum quibusdam. Beatae nihil eos est eaque velit repellendus. Maiores rem unde iure est magni qui consequuntur iure.
See All Comments - 100% Free
WSO depends on everyone being able to pitch in when they know something. Unlock with your email and get bonus: 6 financial modeling lessons free ($199 value)
or Unlock with your social account...