VLOOKUP / COUNTIF formula to assign 0 to certain value

user4259224 Source

From the following formula I am trying to find a way to refer 'E48' value to a specific value in another table. For example, the value here is '0' so it tries to allocate a reference in 0, but I would like to have it refer to the first value mentioned in the table below the row (1 $2,186).

=VLOOKUP($E48,$AZ$45:$BA$50,2,0)*COUNTIF($F48:$AO48,"NL")

AZ        BA  
1   $2,186
1.5 $2,315
2   $2,446
3   $3,514
excelexcel-formula

Answers

answered 4 years ago teylyn #1

As I understand your question you want to return the first value from the table if the lookup value in the Vlookup formula is 0 (which will return an error).

Try

=VLOOKUP(max($E48,1),$AZ$45:$BA$50,2,0)*COUNTIF($F48:$AO48,"NL")

Now the lookup value is either the value in cell E48 or the number 1, whatever is bigger. If E48 is 0, 1 will be looked up. If E48 is 1 or bigger, that value will be used in the Vlookup.

comments powered by Disqus