CODEWITHSUNDEEP
google-sheets
Riyaz Mansoor
Riyaz Mansoor

Reputation: 715

Use vlookup to get exact match while is_sorted is TRUE

Is it possible to use Google Sheets VLOOKUP to get an exact match while using the sorted==TRUE option ?

I didn't find any option in while reading the docs. Asking in case, there is an option I may have missed.

EDIT; I also checked the MATCH function, but the sort option is identical to VLOOKUP - so same situation.

EDIT; an ArrayFormula spreadsheet for users to reference

EDIT; full formula for my purposes for reference using Answer (double VLOOKUP) - very fast for even 20,000 rows

 ARRAYFORMULA(IF(VLOOKUP(B:B,SORT(UNIQUE(B:B)),1,TRUE)=B:B,VLOOKUP(B:B,QUERY(A:B,"select B, count(A) group by B order by B label count(A) ''"),2,TRUE),0))

Upvotes: 2

Views: 1511

Answers (1)

user6655984
user6655984

Reputation:

The double vlookup approach is to search for the primary key itself first, and only if it's found, proceed to retrieving the values from other columns.

If A:B is already sorted by A, and the value we look for is 42, then the formula is: 

=if(vlookup(42, A:B, 1, true) = 42, vlookup(42, A:B, 2, true), na())

where the first vlookup checks if 42 is in column A, and only then passes the job to the second.

If A:B is not sorted, it can be sorted on the fly as in

=if(vlookup(42, sort(A:B), 1, true) = 42, vlookup(42, sort(A:B), 2, true), na())

Upvotes: 3

Related Questions