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
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.