CODEWITHSUNDEEP
excelexcel-2010
114
114

Reputation: 926

Proper Use of the Offset Function

I have the following formula

=IFERROR(IF(FIND(OFFSET($B$2,1,0),$A3,1),VLOOKUP(OFFSET($B$2,1,0),'Keyword list'!B2:E316533,2,FALSE),""),"n/a")

which looks up a value associated with a word if the word is found and otherwise returns "n/a". I have included the OFFSET() function with the hope of making it so that when I move the formula to another column, say from column B to column C, the reference is not still $B$2, and not C2, but B3. Effectively I am trying to make it so that when the formula is dragged across the reference row changes instead of the column, and when I drag down the reference remains fixed at $B$2, $B$3 and so on. Is it possible to use the offset function to do this? Is there a clear mistake I've made in trying to apply it to the above formula? Thanks!

Upvotes: 2

Views: 614

Answers (3)

Jerry
Jerry

Reputation: 71538

You could maybe try the following?

=IFERROR(IF(FIND(OFFSET($B$2,COLUMNS($A:A)-1,0),$A3,1),
 VLOOKUP(OFFSET($B$2,COLUMNS($A:A)-1,0),'Keyword list'!$B$2:$E$31,2,FALSE),""),
 "n/a")

I made a google spreadsheet so that you can try to drag the formula across.

The limitation of that formula is that it will rely on the column of the formula, and it cannot be dragged towards the left in Excel, since that will cause the reference COLUMNS($A:A) to go COLUMNS(#REF!). It can be put in any column then dragged to the right.

Upvotes: 2

Doug Glancy
Doug Glancy

Reputation: 27478

This is untested, but I think it does what you want, i.e., shift the reference down one row, for each column you drag to the right. It uses the COLUMNS function anchored at B in one half and relative in another:

=IFERROR(IF(FIND(OFFSET($B$2,COLUMNS($B:B),1),$A3,1),VLOOKUP(OFFSET($B$2,COLUMNS($B:B),1),'Keyword list'!B2:E316533,2,FALSE),""),"n/a")

Upvotes: 2

sina
sina

Reputation: 1829

I'd go with using INDIRECT to build a reference out of a computed string.

INDIRECT("B"&(2+COLUMN(<current cell>)-COLUMN($B$1)))

This way your reference gets calculated dependent from the offset to column B:

In cell D2 the referenced cell is "B"&(2+COLUMN(D2)-COLUMN($B$1)) = "B"&(2+4-2) = "B4" In cell D3 the reference does not change, as only columns are taken into account.

Same for cell E2: "B"&(2+COLUMN(E2)-COLUMN($B$1)) = "B"&(2+5-2) = "B5"

If your calculation is that fixed, you could even only do with COLUMN(E2) as 2 and COLUMN(B1) cancel each other out: INDIRECT("B"&COLUMN(<current cell>)))

Upvotes: 1

Related Questions