Excel Vlookup

jtej009 · #1 02-09-2011, 06:38 PM

How do you use vlookup to get the next best value

A B

BP
BP UK
BP UK
BP
BP UK
BP

How do you use Vlookup to get UK for BP instead of 0

Colin Legg · #2 02-10-2011, 03:25 AM

Hi,

VLOOKUP() will always return the first match found so, if you search column A for "BP" then you will get "" returned.

If you want to return the first value where column A equals "BP" and column B is not empty, one formula option is:

Code:

=INDEX(B2:B7,MATCH(1,(A2:A7="BP")*(B2:B7<>""),0))

This is an array formula, so when you type it into the formula bar with CTRL+SHIFT+ENTER, not just ENTER.

If you want to return multiple results (say ALL results where column A equals "BP" and column B is not empty) then you should use the advanced filter instead of formulas.

Hope that helps...

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Using Vlookup & IF together	thelauncher	Excel	5	08-25-2013 11:32 PM
Can i do this with a VLookup?	foodstudent	Excel	1	01-21-2011 12:34 AM
Using IF & VLOOKUP together	junction	Excel	7	11-18-2010 05:15 AM
Help with VLOOKUP	sakhtar	Excel	2	07-24-2010 07:39 PM
VLOOKUP in Excel with grade book.	Sailorcancer	Excel	3	04-15-2010 09:55 PM