Excel Formula
Solved/Closed
sephiek
sephiek - Sep 13, 2013 at 11:20 AM
- Posts
- 2
- Registration date
- Wednesday September 11, 2013
- Status
- Member
- Last seen
- September 12, 2013
sephiek - Sep 13, 2013 at 11:20 AM
Related:
- Excel Formula
- @ in excel formula ✓ - Forum - Excel
- Excel formula if cell contains text then return value in another cell ✓ - Forum - Excel
- How to ignore false in excel formula ✓ - Forum - Office Software
- Number to words in excel formula - Guide
- Excel formula to transfer data between worksheets - Guide
6 replies
Mazzaropi
Sep 12, 2013 at 08:38 AM
- Posts
- 1963
- Registration date
- Monday August 16, 2010
- Status
- Contributor
- Last seen
- April 25, 2022
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.
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.
Mazzaropi
Sep 12, 2013 at 05:37 PM
- Posts
- 1963
- Registration date
- Monday August 16, 2010
- Status
- Contributor
- Last seen
- April 25, 2022
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.
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.
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!!
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
Sep 12, 2013 at 02:53 PM
- Posts
- 1963
- Registration date
- Monday August 16, 2010
- Status
- Contributor
- Last seen
- April 25, 2022
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.
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.
Didn't find the answer you are looking for?
Ask a question
sephiek
Sep 12, 2013 at 03:15 PM
- 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..