1

so I have the following formula and I'm trying to create a stable formula where $o4$p74 is stapled and wont change. However, I need to constantly delete the p-column and then I do that, the vlookup changes from p to o and continues if I keep deleting the P-column. I'm trying so that it always returns the P-column value in which is always getting deleted, is there any ideas of how to go about this?

=VLOOKUP(B437,BHR8732A2!$O4:$P74,2,FALSE)*$S437

Sergio Oropeza
  • 37
  • 1
  • 5

3 Answers3

1

Another solution based on Allen's would be:

=VLOOKUP(B437,indirect("'BHR8732A2!'$O4:$P74"),2,FALSE)*$S437

Keeps it all nicely in a cell.

Delta_zulu
  • 1,580
  • 1
  • 10
  • 9
0

Have you thought about putting an =P1 before the column you are deleting? That would give you a ref error on the delete but I think it is easier to deal with that then messing with the vlookp each time.

Just found "=indirect("F2")" here

You could do that int the vlook up or using the suggestion above

Community
  • 1
  • 1
Allen Craig
  • 127
  • 10
  • where would I Add it? – Sergio Oropeza Sep 29 '16 at 21:49
  • Check out @Delta_Zulu above that should do it. – Allen Craig Sep 30 '16 at 13:01
  • Example Use =VLOOKUP(B437,BHR8732A2!$O4:$P74,2,FALSE)*$S437 – Allen Craig Sep 30 '16 at 13:02
  • On BHR8732A2!$O4:$Q74 column 2 would be "=indirect(Q4)" and Q4 would be the column you would delete after the lookup. I think the bigger questions might be why you are doing this, if you want to return a value across several columns You could write one set of nested IFNA() statements for the number of columns you are testing Example: IFNA(VLOOKUP(B437,BHR8732A2!$O4:$P74,2,FALSE),VLOOKUP(B437,BHR8732A2!$O4:$Q74,3,FALSE))*$S437 – Allen Craig Sep 30 '16 at 13:08
  • You can do a look up specific to each column you are testing and then just include that number of IFNA you need to test every column (the final one set to return blank or some value if its not found in any column. – Allen Craig Sep 30 '16 at 13:08
0

You can use Offset to resize a reference to column O only, like this

=VLOOKUP(B437,OFFSET(BHR8732A2!$O4:$O74,,,,2),2,FALSE)*$S437

Note: this will make the formula Volitile

chris neilsen
  • 52,446
  • 10
  • 84
  • 123