vlookup with updating table array range...what is the best solution?

[PP3321]

Dear Chandoo Community,

Thank you so much always for your generous support...
I have 1 question today...

I need to do vlookup every week.
Problem is that I need to paste new rows to update the vlookup table every week too.
This changes the range for the table...

Please find the attached worksheet for example.

*My current solution is to calculate last non-empty row using array formula.
Then hard-code the new range.

*I tried indirect function.
But it was not good solution.
My table contains +10,000 rows,
and it became slow as it re-calculates each time...

*I prefer NOT to use VBA if possible...

 

[NARAYANK991]

Hi ,

I am not able to understand how just 2 formulas in a worksheet can slow it down.

Can you clarify ?

Narayan

 

[PP3321]

@NARAYANK991
Thank you for your question.
I have many tabs in the original excel file.
Using Indirect Function seems to slow down a bit.

Is there any other ways other than using Indirect Function?

 

[NARAYANK991]

Hi ,

However many tabs there may be in a workbook , if a worksheet has just 2 formulas , it cannot slow down the recalculation of that worksheet.

Probably if you can upload your actual workbook , it may be clear what the problem is.

Narayan

 

[Haseeb A]

Hello,

Why can't you simply use INDEX/MATCH function?

=INDEX(C:C,MATCH(L2,B:B,0))

If you really wants to calculate last row use MATCH function which is much faster.

F2, array entered

=MAX(IFERROR(MATCH({9E+300,"zzzzzzzzzzzzzz"},C:C),1))

M2,

=VLOOKUP(L2,INDEX(B:B,3):INDEX(C:C,F2),2,0)

Change starting row in red highlighted.

 

[bosco_yip]

Maybe,

1] In F2,change this "Max Row" formula

{=MAX((C:C<>"")*(ROW(C:C)))}

p.s. This formula is very expensive, being 1 million cells x 1 million cells calculation, and will cause the computer slow down

Into this

=MATCH("zzzz",C:C)

and,

2] In M2, change this "Lookup" formula

=VLOOKUP(L2,INDIRECT("$B$3:$C"&$F$2),2,0)

Into this

=VLOOKUP(L2,B3:INDEX(C:C,F2),2,0)

3] However, you can use this dynamic range formula without the helper cell

=VLOOKUP(L2,B3:INDEX(C:C,MATCH("zzzz",C:C)),2,0)

Regards
Bosco

 

[PP3321]

@NARAYANK991 @Haseeb A @bosco_yip
Thank you so much for your help...Excel is so beautiful...