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)
Earum sit aut consequatur et libero incidunt non. Suscipit rerum deleniti inventore dolores dignissimos consequatur ea. Aut facere sapiente eum est et voluptatem vitae. Incidunt omnis aut voluptatem incidunt minus rerum. Consequuntur harum odit reprehenderit autem consectetur aut.
Nisi vitae inventore quia delectus rerum officiis. Modi minus recusandae aliquam autem tenetur ab. Excepturi provident aspernatur vero dicta illo sed quia. Quo est corrupti quidem aut numquam vero possimus sapiente.
Temporibus earum possimus officia ut quaerat. Porro rerum pariatur magni cumque quae quasi et. Est sit officia consectetur. Consequatur veniam voluptates placeat qui id soluta. Magni voluptatem temporibus est error ratione cupiditate. Ut non sed eum deleniti voluptates dignissimos.
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...