Vlookup

[Ragarm13]

I have a formula that works great:

=VLOOKUP($D4,CHOOSE({2,1},$B$3:$B$25,$O$3:$O$25),2,0)

What I am wondering now is if the value in O3:O25 is = 0, how can I keep the cell from displaying the information from B3:B25?

I tried an IF but couldn't get it to work as it put a '0' in the cell.

 

[sheetspread]

What do you want to appear instead? A "" ?

 

[BiocideJ]

That is a complicated VLOOKUP that basically does what a basic INDEX/MATCH formula can do.

I think this should do what you are looking for. An IF function should do what you are wanting.
NOTE: You can edit the text portions to display whatever you want (including "" if you just want blanks)

=IFERROR(IF($D4=0,"can't use zero",INDEX($B$3:$B$25,MATCH($D4,$O$3:$O$25,0))),"invalid response")