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?
| +152 | How to stop feeling like shit for not making it in IB? | 57 | 4h | |
| +115 | If Tik Tok is forced to sell, what banks do you think would be involved in the deal? | 58 | 3h | |
| +80 | Updated LA Banking Scene (2024) | 57 | 4h | |
| +38 | Ranking banks that went under | 20 | 11h | |
| +34 | Burnt Out M&A ASO | 18 | 9h | |
| +32 | A strategy for SA applicants late to the game like myself | 15 | 15h | |
| +29 | Relevance of A-Levels for U.K. London recruiting | 21 | 21h | |
| +26 | Series 79 Help / Tips to Pass The First Attempt | 11 | 2d | |
| +26 | Intern Ettiquette | 4 | 19h | |
| +23 | What are hours like at BBs in London? | 47 | 17h |
Career Resources
Get instant access to lessons taught by experienced private equity pros and bulge bracket investment bankers including financial statement modeling, DCF, M&A, LBO, Comps and Excel Modeling.
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)
Repellat nihil rerum voluptatibus et est. Expedita quis explicabo vero. Perferendis aut molestiae nihil.
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...