Results 1 to 4 of 4

Thread: Vlookup non blank

  1. #1

    Default Vlookup non blank

    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


    BE0000303124NRNRNRAAuAAuA-1+uA-1+u
    XS0223635730A-A-A-A-A-A-2A-2
    XS0693220005A+A+A-1A-1
    XS0693854605A+A+A+A+A+A-1A-1
    XS0276891594A-A-A-A-A-A-2A-2
    XS0354843533A+ /*-A+*-A+A+ *-A+ *-A-1A-1
    XS0405664813AA- /*-AA-*-AA-AA- *-AA- *-A-1+ *-A-1+ *-
    XS0270776411BBBBBBBBB
    XS0263593765A-A-A-A-A-A-2A-2
    USL0302DAN32BBB-BBB-BBB-BBB-BBB-A-3A-3
    DE0001134468NRNRNRAAAuAAAuA-1+uA-1+u
    XS0412447632A-A-A-
    XS0279766629NRNRNRBBB+BBB+A-2A-2
    XS0279585169BBB+BBB+BBB+A+A+A-1A-1
    XS0282586311A-A-A-A-A-A-2A-2
    BBG00000DW03NRNRNRA-A-A-2A-2
    PP6W0NMO6A-A-2A-A-A-2A-2
    PP6W0NMM0A+ *-A-1A+ *-A+ *-A-1A-1
    PPRN53NR4A *-A-1 *-A-1 *-A-1 *-A-1*-A *-A *-A-1 *-A-1 *-
    PPRN53NQ6A+ *-A-1A-1A-1A-1A+ *-A+ *-A-1A-1
    PP6W0NML2AA- *-A-1+ *-A-1+ *-A-1+ *-A-1+*-
    PPRN53NTOBBBA-2A-2A-2A-2
    PPRN53NS2AA-1

  2. #2
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    73,092

    Default Re: Vlookup non blank

    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

  3. #3

    Default Re: Vlookup non blank

    hi Andrew - this seems to work . Thank you very much

  4. #4

    Default Re: Vlookup non blank

    2 5

    the above is example,i want first non blank cell ie 2,but 2 and 5 are formula based values...



    formula used for 2 and 5 are =IF(L16=" ",IF(COUNTIF($J$14:$J$15,M4)>=1," ",M4),L16)

    when i tried =IF(K16<>"",K16,IF(L16<>"",L16,IF(M16<>"",M16,N16))) for non blank cell,it says error,pls help
    Last edited by satvislbdvt; Jan 22nd, 2017 at 09:41 AM.

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com