200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
Dear Excel Forum, (I m sorry for inconvenience)
I have a real job problem. Some subledgers are allotted to particular officers. A few alphabets (subledgers) are located in data tables with header (dealing officer name). So, when, I type any alphabet then, corresponding header of the table should be populated.
I know very well Vlookup, Nested IF. So, plz try with Indirect or any other formulae. Thanx with hope of prompt reply. (Plz c the attached file)
~ Nitin Shukla
For the purpose of using the formula below you need to set up a named range "Ledgers" which comprises the table C10:F19 in your example. The names of the officers must be in the row immediately above this range.
=INDEX(OFFSET(INDEX(Ledgers,1,0),-1,0),1,SUMPRODUCT(--(ISERROR(FIND(H11,Ledgers))=FALSE)*COLUMN(Ledgers))-2)H11 refers to the cell H11 in your example. I tested also H12:H14.
-2 is the number of columns on the left of the named range "Ledgers". In your example Ledgers starts in column C. So there are 2 columns to the left of it and hence -2. This adjustment could have been avoided by calculating that number instead of hard-coding it but I decided asgainst that course of action because it would bloat the formula more than its worth. If you place the Ledgers table in column A no adjustment will be required.
Of course, you can replace the references to Ledgers with hard-coded ranges which would also shorten the formula because OFFSET(INDEX(Ledgers,1,0),-1,0) translates to $C9:$F$9. In fact, in the first version of this formula I used A9:F9 which doesn't require an adjustment. I decided to accept a little extra length for the benefit of grater transparency.
BTW, the problem hails from the FIND function which returns a sheet column. Perhaps another function can be made to return a range column instead.