Menu
  1. Accueil
  2. Tech Forum
  3. Office Software
  4. Excel

Excel Formula [Solved/Closed]

Report
Posts
2
Registration date
Wednesday September 11, 2013
Status
Member
Last seen
September 12, 2013
 -
 sephiek -
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.
See more

6 replies

Answer 1 / 6
Posts
1837
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
October 17, 2020
 136
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.

Thank you
1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

Answer 2 / 6
Posts
1837
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
October 17, 2020
 136
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.
Thank you
1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

Answer 3 / 6

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!!
Answer 4 / 6
Posts
1837
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
October 17, 2020
 136
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.
Answer 5 / 6
Posts
2
Registration date
Wednesday September 11, 2013
Status
Member
Last seen
September 12, 2013
It tells me I've entered to many arguments for this function..
Answer 6 / 6

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

: )
Ask a question