0

Having a problem with VLOOKUP under Google Sheets.

I have this basic command that I am trying to assign to each row of a column (there are 200 rows):

=Vlookup(D1,KDCAssets!A1:F500,5,false)

I want to assign this to a column. When doing so, I am expecting something like this:

Row 1    =Vlookup(D1,KDCAssets!A1:F500,5,false)
Row 2    =Vlookup(D2,KDCAssets!A1:F500,5,false)
Row 3    =Vlookup(D3,KDCAssets!A1:F500,5,false)
Row 4    =Vlookup(D4,KDCAssets!A1:F500,5,false)

The D value increases but A and F values remain the same ( i.e. A1 and F500 ). But what I am getting is something like:

Row 1    =Vlookup(D1,KDCAssets!A1:F500,5,false) 
Row 2    =Vlookup(D2,KDCAssets!A2:F501,5,false) <- increases by 1
Row 3    =Vlookup(D3,KDCAssets!A3:F502,5,false) <- increases by 1
Row 4    =Vlookup(D4,KDCAssets!A4:F503,5,false) <- increases by 1

The range values starting with A1 and F500 increases with each successive row. But, for the lookup, I need to be the same for each row. How can I get this to act properly?

TIA

Casey Harrils
  • 2,793
  • 12
  • 52
  • 93
  • 2
    `=Vlookup(D1,KDCAssets!A$1:F$500,5,false)`. Reference: [Why use dollar sign ($) in Excel formulas - absolute and relative cell references](https://www.ablebits.com/office-addins-blog/relative-absolute-reference-excel/) – idfurw Oct 03 '22 at 07:34
  • Thanks for the response. This is what I wound up using: =IF(ISBLANK(D7), "", Vlookup(D7,KDCAssets!A:F,5,false) ) – Casey Harrils Oct 03 '22 at 07:50

1 Answers1

1

You can use new lambda function BYROW() to make it dynamic spill array.

=BYROW(D1:INDEX(D1:D,COUNTA(D1:D)),LAMBDA(x,Vlookup(x,KDCAssets!A1:F,5,false)))

  • Here D1:INDEX(D1:D,COUNTA(D1:D)) will return a array of values as well cell reference from D1 to last non empty cell in column D (Assume you do not have any blank rows inside data). If you have then you have to use different approach. See this post by @TheMaster

  • Then LAMBDA() will apply Vlookup() function for each cell of D column.

Harun24hr
  • 30,391
  • 4
  • 21
  • 36