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...

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

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