Excel Formula

Solved/Closed
sephiek
Posts
2
Registration date
Wednesday September 11, 2013
Status
Member
Last seen
September 12, 2013
- Sep 11, 2013 at 01:17 AM
 sephiek - Sep 13, 2013 at 11:20 AM
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.

6 replies

Mazzaropi
Posts
1963
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
April 25, 2022
147
Sep 12, 2013 at 08:38 AM
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.

1
Mazzaropi
Posts
1963
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
April 25, 2022
147
Sep 12, 2013 at 05:37 PM
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.
1
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!!
0
Mazzaropi
Posts
1963
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
April 25, 2022
147
Sep 12, 2013 at 02:53 PM
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.
0

Didn't find the answer you are looking for?

Ask a question
sephiek
Posts
2
Registration date
Wednesday September 11, 2013
Status
Member
Last seen
September 12, 2013

Sep 12, 2013 at 03:15 PM
It tells me I've entered to many arguments for this function..
0
Oh My Gosh !!! You are a GENIUS!!!! Thank you SO, SO much!!!
This has worked for me, and thank you again !

: )
0