VLOOKUP formula with dynamic address (second parameter), depending on current cell

Question

I need a VLOOKUP that doesn't search the entire range of the column, but from an address that changes depending on the current cell.

company1    user1    01/2020
fixed       val2     1200,00
fixed       val2     1100,00
fixed       val15    3000,00
company1    user2    01/2020
fixed       val13    500,00
fixed       val65    200,00

What I'm trying is to make the VLOOKUP search the values of each "user". I already identified the "header" of each user with another formula to get the correct address, so I can VLOOKUP it.

So the formula is:

VLOOKUP(<value to be found>,<formula to start cell> : <formula to last cell>,<return column number>)

The problem is that the addresses comes with quotes no matter what I do.

The formula to get the start cell part is the problem. I tried with NO sucess:

CELL("address",INDIRECT(<cell with the address without quotes>,1))

Outside the VLOOKUP it returns without quotes, but inside the formula the quotes are always present. Even if I use ADDRESS() inside the formula above it returns with quotes anyway. Can somebody help me with that?

Answer 1

Not sure I understand what you're doing, but if your startCell and endCell formulas store the resultant addresses in, for example A2 and A3, then the array argument for VLOOKUP would be INDIRECT(A2):INDIRECT(A3)

A2 and/or A3 could be replaced by any formula that returns the address as a string.

In the long run, you would be better served by having a better organized data source.

Answer 2

If I was approaching this from scratch the strategy I would use is to create a composite index column containing a combined user+item value and then lookup on that, achieving what I understand to be your objective in a relatively clean and simple way that avoids nesting functions and performing row-math that could easily be one or two rows out and cause intermittent errors.

Example below: Formula for composite index and value lookup at bottom.

The composite index column combines "user" and "item" with a colon inbetween (if you need to have a colon in the user name choose another character that is never in the user name). When a row is "fixed" it extracts the user name from the row above by finding the text before the colon and adds the item name to it. If the row is not "fixed" then it changes the current user. (A '#' is added to the 'user' row to stop the formula matching against a blank search value)

The composite index column now contains all the information you need and a simple index/match against "user:item" will get your answer.

FYI: INDEX() is generally a faster, more flexible and more efficient than VLOOKUP(). Also functions like ADDRESS() and OFFSET() cause unnecessary recalculations on the worksheet because Excel's calculation engine can't work out in advance which parts of the sheet they are getting data from. (They are called 'volatile' functions and can slow things down considerably on large sheets.)