Vlookup

Contributor

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:
https://support.microsoft.com/en-us/office/vlookup-function-0bbc8083-26fe-4963-8ab8-93a18ad188a1
What did you want to achieve?

@Jamesboden 

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.

@Jamesboden 

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.

@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)

@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
)