PDA

View Full Version : VLookup to skip blank cell



jeffrani
03-20-2017, 04:55 AM
Hi, I need to figure out how to make cell J3 to give a respond as Lvl 1. So I was hoping that if for example: Steven has 3 roles which cover from Level 1 to Level 3, If I am to empty the cell that list Steven in Level 1, J3 would respond Steven as Level 2 and so on. As of right now the only idea i got is by using a regular vlookup, like =VLOOKUP(I3,A9:C14,3,FALSE) then delete per row for unwanted row, making the vlookup work by letting it find the most upper connection it can made. I was just wondering, is there a better way of doing it apart from doing it manually like this?

mdmackillop
03-20-2017, 06:19 AM
Formula array {=MIN(IF(($A$9:$A$14=I3)*($C$9:$C$14>0),$C$9:$C$14))}
and cheat by custom formatting numbers as "Lvl "0 to avoid text comparisoins