Forum Discussion

Jamesboden's avatar
Jamesboden
Copper Contributor
Aug 12, 2022

Vlookup

Hey Guys.    I'm hoping someone can assist me but my vlookup doesn't seem to be searching the whole sheet, Just the first column. Vlookup, Excel I'm using =VLOOKUP(A2, Sheet1!A:AJ, 2, FALSE) Can ...
excel
Jamesboden's avatar
Jamesboden
Copper Contributor
Aug 12, 2022
Hey. I want to look up to search the whole of sheet1.
HansVogelaar's avatar
HansVogelaar
MVP
Aug 12, 2022

Jamesboden 

So what exactly do you want to do if the word "Success" is found anywhere in the sheet?

  • Jamesboden's avatar
    Jamesboden
    Copper Contributor
    Aug 12, 2022
    so everything has a postcode in the next collum. So if it finds Success anywhere I want it to return the postcode for it.
    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Aug 12, 2022

      Jamesboden 

      As variant

      =INDEX( Sheet1!A1:X1000,
        SUMPRODUCT(  ( Sheet1!A1:X1000="Success")*ROW(Sheet1!A1:X1000) ) - ROW(Sheet1!$A$1)+1,
        SUMPRODUCT(  ( Sheet1!A1:X1000="Success")*COLUMN(Sheet1!A1:X1000) ) - COLUMN(Sheet1!$A$1)+1 +1
)
    • HansVogelaar's avatar
      HansVogelaar
      MVP
      Aug 12, 2022

      Jamesboden 

      Searching the entire sheet  has a terrible impact on performance. In the following, I used A1:AJ100.

      If you don't have Microsoft 365 or Office 2021, confirm the formula with Ctrl+Shift+Enter.

       

      =INDEX(INDEX(Sheet1!A1:AJ100,MAX((Sheet1!A1:AJ100="Success")*ROW(A1:A100)),0),MATCH("Success",INDEX(Sheet1!A1:AJ100,MAX((Sheet1!A1:AJ100="Success")*ROW(A1:A100)),0),0)+1)