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 anyone please advise. 


8 Replies
This is exactly what VLOOKUP() does. It searches the first column and returns the content according to the column index.
See also here:
What did you want to achieve?


VLOOKUP searches for the lookup value (A2) in the first column of the lookup range (i.e. Sheet1 column A). If it finds a match, it returns the value from the column of the lookup range specified in the 3rd argument. In your example, the column index is 2, so it would return a value from Sheet1 column B.

Hey. I want to look up to search the whole of sheet1.
OOOH! that where I'm going wrong. So if my return was in column B is wouldn't just go two cells across.


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

so everything has a postcode in the next collum. So if it finds Success anywhere I want it to return the postcode for it.


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.




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