Excel FormulaSolved/Closed

Question

I have a blank excel invoice that I want to be able to type the Item number on it and have it populate the vital information on it like the item name, price, etc.

I have another excel spread sheet with all of the information on it.

I want to tie these two together by having the invoice lookup and return the information from the spreadsheet.

What would be the best type of formula to use in doing this?  Would VLookup be good, could someone give me an example?

Please.

Mazzaropi · Answer

Sephiek, Good Morning.

VLookup is the right function to do this job.

Suppose your invoice file is INVOICE-SEPHIEK.xlsx and your data file is DATA-SEPHIEK.xlsx

INVOICE-SEPHIEK.xlsx
..............A...................B............C......
1.....Item Number......NAME......PRICE
2.........123987..............1 ?..........2 ?

DATA-SEPHIEK.xlsx
..............A...................B...........C....
1.....Item Number.....NAME.....PRICE
2.........120000.........AAAA.......100
3.........120444.........BBBB.......150
4.........123987.......CCCC........50
5.........135200.........DDDD........67

At 1 ? on INVOICE-SEPHIEK.xlsx do this:
B2 --> =VLOOKUP(A2,[DATA-SEPHIEK.xlsx]Plan1!$A$2:$C$5,2,0)

At 2 ? on INVOICE-SEPHIEK.xlsx do this:
C2 --> =VLOOKUP(A2,[DATA-SEPHIEK.xlsx]Plan1!$A$2:$C$5,3,0)

It's done!

I hope it helps you.

Tell us if it worked for you.

sephiek · Answer

It works great !!!  Thank you!!  I have one other question.

If I fill the invoice cells with the formula, where nothing is it shows #N/A, how can I get it not to show #N/A in the empty fields?

Thank you so much for your help!!

Mazzaropi · Answer

Sephiek, Good Afternoon. 

To avoid this message you can use a ISERROR function.

Before:
At 1 ? on INVOICE-SEPHIEK.xlsx do this:
B2 --> =VLOOKUP(A2,[DATA-SEPHIEK.xlsx]Plan1!$A$2:$C$5,2,0) 

NOW:
At 1 ? on INVOICE-SEPHIEK.xlsx do this:
B2 --> =IF(ISERROR(VLOOKUP(A2,[DATA-SEPHIEK.xlsx]Plan1!$A$2:$C$5,2,0),"",VLOOKUP(A2,[DATA-SEPHIEK.xlsx]Plan1!$A$2:$C$5,2,0))

Try this one and tell me if it worked for you.
I hope it helps you.

sephiek · Answer

It tells me I've entered to many arguments for this function..

Mazzaropi · Answer

I believe that's missing a ) in the formula. 

Try this one now:
=IF(ISERROR(VLOOKUP(A2,[DATA-SEPHIEK.xlsx]Plan1!$A$2:$C$5,2,0)),"",VLOOKUP(A2,[DATA-SEPHIEK.xlsx]Plan1!$A$2:$C$5,2,0))

I hope it helps you.

sephiek · Answer

Oh My Gosh !!!  You are a GENIUS!!!!  Thank you SO, SO much!!!
This has worked for me, and thank you again !

: )

Excel Formula

6 replies

Subscribe To Our Newsletter!