With your sample data in A1:M23 and the lookup value in N1 try:
=INDEX(B1:M23,MATCH(N1,A1:A23,FALSE),MATCH(TRUE,INDEX(INDEX(B1:M23,MATCH(N1,A1:A23,FALSE),0)<>"",),FALSE))
This is a discussion on Vlookup non blank within the Excel Questions forums, part of the Question Forums category; Hi, I am looking to do a vlookup that returns the first non blank cell. the data i am using ...
Hi, I am looking to do a vlookup that returns the first non blank cell. the data i am using is below - I want to look up XS0270776411 for instance and return the first populated cell, in this case E8 with a value of BBB. Have tried array, but didn't get it to work. Any help , much appreciated
BE0000303124 NR NR NR AAu AAu A-1+u A-1+u XS0223635730 A- A- A- A- A- A-2 A-2 XS0693220005 A+ A+ A-1 A-1 XS0693854605 A+ A+ A+ A+ A+ A-1 A-1 XS0276891594 A- A- A- A- A- A-2 A-2 XS0354843533 A+ /*- A+ *- A+ A+ *- A+ *- A-1 A-1 XS0405664813 AA- /*- AA- *- AA- AA- *- AA- *- A-1+ *- A-1+ *- XS0270776411 BBB BBB BBB XS0263593765 A- A- A- A- A- A-2 A-2 USL0302DAN32 BBB- BBB- BBB- BBB- BBB- A-3 A-3 DE0001134468 NR NR NR AAAu AAAu A-1+u A-1+u XS0412447632 A- A- A- XS0279766629 NR NR NR BBB+ BBB+ A-2 A-2 XS0279585169 BBB+ BBB+ BBB+ A+ A+ A-1 A-1 XS0282586311 A- A- A- A- A- A-2 A-2 BBG00000DW03 NR NR NR A- A- A-2 A-2 PP6W0NMO6 A- A-2 A- A- A-2 A-2 PP6W0NMM0 A+ *- A-1 A+ *- A+ *- A-1 A-1 PPRN53NR4 A *- A-1 *- A-1 *- A-1 *- A-1 *- A *- A *- A-1 *- A-1 *- PPRN53NQ6 A+ *- A-1 A-1 A-1 A-1 A+ *- A+ *- A-1 A-1 PP6W0NML2 AA- *- A-1+ *- A-1+ *- A-1+ *- A-1+ *- PPRN53NTO BBB A-2 A-2 A-2 A-2 PPRN53NS2 A A-1
With your sample data in A1:M23 and the lookup value in N1 try:
=INDEX(B1:M23,MATCH(N1,A1:A23,FALSE),MATCH(TRUE,INDEX(INDEX(B1:M23,MATCH(N1,A1:A23,FALSE),0)<>"",),FALSE))
Microsoft MVP - Excel
hi Andrew - this seems to work . Thank you very much
Like this thread? Share it with others