Excel lookup

Matt Pinkston
Matt Pinkston used Ask the Experts™
on
I have a workbook with two tables (Q2 & BPS)

Q2 is the main and receiving sheet column A (ID) and column B (BPS Status "empty")
BPS is where I want to pull data from column A (ID) and column B (BPS Status)

I need a lookup that will put the contents of column BPS-B into Q2-B when Q2-A = BPS-A
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
I think I understand but I'm not positive. Would you be able to post the workbook itself?

Commented:
Please check the attached example to see if this is what you want to do...

jppinto
VLOOKUP.xlsx
Matt PinkstonEnterprise Architect

Author

Commented:
When I try that code it appears to partially work.  Many of the values come up #N/A
Angular Fundamentals

Learn the fundamentals of Angular 2, a JavaScript framework for developing dynamic single page applications.

Commented:
They came like #N/A when it doesn't find a match, right?
Matt PinkstonEnterprise Architect

Author

Commented:
and some = 0
Matt PinkstonEnterprise Architect

Author

Commented:
yes on the #N/A

Commented:
Can you post a sample file, where you're testing the formula?
Matt PinkstonEnterprise Architect

Author

Commented:
So it appears that if there is a match but the value is blank in BPS then it puts in a 0 and if there is now match you get #NA

is there a way around that?
Commented:
What would you like it to do if there is no match, or if it is blank?

Using Cell B2 in jppinto's example, you could change it to this:
=if(isna(VLOOKUP(A2,BPS!A$2:B$12,2,FALSE)),"DO THIS IF IS #NA",VLOOKUP(A2,BPS!A$2:B$12,2,FALSE))

In that case, every time there is no match it will say "DO THIS IF IS #NA" - just change that to whatever you'd like to see there.  
Top Expert 2010

Commented:
Or if you are using Excel 2007/2010:

=IFERROR(VLOOKUP(...),"value if error")

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial