vlookup & if

P

pantakos

Board Regular
Joined
Oct 10, 2012
Messages
158
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Hello,

I need to make an excel that contains about 5 sheets. At an other sheet I have to enter the value of a cell from a different sheet but using vlookup, I also have to check if the value is not empty (anything >0). So I have to combine vlookup and IF functions.
How can I use vlookup to enter values to a different sheet? I tried but no luck. I dont know much about excel I am technician PC (cable management etc).
Can I find an example somewhere?
If needed I can upload the excel as example.
Thank you!
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
Sort by date Sort by votes
what if the value is Empty , what do you want to return
=IF( TEST, TRUE, FALSE)
=IF( Vlookup(,,,,) = 0 , "what to do if zero", Vlookup(......) )

see here
chemistry_v1.xlsx
ABCDEF
1Lookup ValueVlookupVlookup NO 0TABLE
21AA1A
320contains a zero2
43BB3B
540contains a zero4
6
Sheet8
Cell Formulas
RangeFormula
B2:B5B2=VLOOKUP(A2,$E$2:$F$8,2,FALSE)
C2:C5C2=IF(VLOOKUP(A2,$E$2:$F$8,2,FALSE)=0, "contains a zero", VLOOKUP(A2,$E$2:$F$8,2,FALSE))
 
Upvote 0
etaf said:
what if the value is Empty , what do you want to return
=IF( TEST, TRUE, FALSE)
=IF( Vlookup(,,,,) = 0 , "what to do if zero", Vlookup(......) )

see here
chemistry_v1.xlsx
ABCDEF
1Lookup ValueVlookupVlookup NO 0TABLE
21AA1A
320contains a zero2
43BB3B
540contains a zero4
6
Sheet8
Cell Formulas
RangeFormula
B2:B5B2=VLOOKUP(A2,$E$2:$F$8,2,FALSE)
C2:C5C2=IF(VLOOKUP(A2,$E$2:$F$8,2,FALSE)=0, "contains a zero", VLOOKUP(A2,$E$2:$F$8,2,FALSE))
Click to expand...
Thank you for your reply. I will test right now and let you know.
Thank you!
 
Upvote 0
etaf said:
what if the value is Empty , what do you want to return
=IF( TEST, TRUE, FALSE)
=IF( Vlookup(,,,,) = 0 , "what to do if zero", Vlookup(......) )

see here
chemistry_v1.xlsx
ABCDEF
1Lookup ValueVlookupVlookup NO 0TABLE
21AA1A
320contains a zero2
43BB3B
540contains a zero4
6
Sheet8
Cell Formulas
RangeFormula
B2:B5B2=VLOOKUP(A2,$E$2:$F$8,2,FALSE)
C2:C5C2=IF(VLOOKUP(A2,$E$2:$F$8,2,FALSE)=0, "contains a zero", VLOOKUP(A2,$E$2:$F$8,2,FALSE))
Click to expand...
Ok to explain it better

I have a excell worksheet that containts about 3 unique tabs (sheets) AUDIO , LIGHTS and PROFORMA.

AUDIO is

SPEAKERS QTYPRICE PER DAYPCS
CLAIR BROS C1216€110,000
CLAIR BROS C8 16€50,000
CLAIR BROS CS1184€50,000
L-ACOUSTICS V-DOSC40€80,000
L-ACOUSTICS dV-DOSC114€30,000
L-ACOUSTICS KUDO6€60,000
L-ACOUSTICS SYVASET L-R€310,000
L-ACOUSTICS ARCS WIDE16€40,000
L-ACOUSTICS X1524€45,000
L-ACOUSTICS X1224€35,000
L-ACOUSTICS X816€30,000
L-ACOUSTICS FM11518€25,000
L-ACOUSTICS XT-1158€25,00€0,00
L-ACOUSTICS XT-128€20,000
L-ACOUSTICS MTD 108A12€20,000
L-ACOUSTICS KS 2824€80,000
L-ACOUSTICS dV-SUB16€25,000
L-ACOUSTICS SB1816€35,000
EAW SB 100050€25,000
NEXO PS1547€20,000
NEXO PS 1012€20,000
ELECTROVOICE ELX112p18€20,000
SLS LS 880072€20,000
TOTAL€0,00
DESKS - DJ SET - SPLITTERQTYPRICE PER DAYPCS
DIGICO SD-12 962€350,000
DIGIRACK 56/323€75,000
MINIRACK 24/81€30,000
YAMAHA CL-5 1€350,000
YAMAHA RIO 3224-D2 RACK 32/162€75,000
YAMAHA DM 20003€100,000
YAMAHA 01V1€50,000
YAMAHA MG-122€20,000
MIDAS HERITAGE-D2€400,000
MIDAS PRO22.4€200,000
MIDAS M321.2€100,000
MIDAS M32R2€75,000
MIDAS DL 251 (48I- 16O)3.2€50,000
MIDAS DL 431 (24IN -SPLIT)4€60,000
MIDAS DL 451 3SLOT4€30,00€0,00
MIDAS VENICE 1€50,00€0,00
DIGIDESIGN PROFILE1€350,00€0,00
PIONEER DJSET NEXUS 2 1€180,00€0,00
PIONEER CDJ 3502€30,00€0,00
TECHNICS PICK-UP MKII2€30,00€0,00
SPLITTER SYSTEM BSS 24CH.4€100,00€0,00
SPLITTER SYSTEM YPSILON 48CH.2€150,00€0,00
SPLITTER SYSTEM UTG 48CH. 2€200,000
TOTAL€0,00
MISCELLANEOUSQTYPRICE PER DAYPCS
MIC STAND KICK€2,000
MIC STAND SHORT€2,000
MIC STAND TALL€2,000
MIC STAND STRAIGHT€2,000
MIC STAND ULTIMATE€5,000
SPEAKER STAND TRIPOD€2,000
SPEAKER STAND ROUND BASE€3,000
0
MAC BOOK PRO 13''2€30,000
LENOVO YOGA 530 14''2€20,000
FOCUSRITE SCARLET 212€10,000
BEYER DYNAMIC MM1 ANALYZER3€5,000
2 YAMAHA NET. SWITCH SWP1-8MMF1€60,000
0
AVALON 737€30,000
FOCUSRITE RED 3€30,00€0,00
XTA DYNAMIC€10,000
BSS DYNAMIC€10,000
DRAWMER DYNAMIC€10,000
KLARK TEKNIK DYNAMIC€10,00€0,00
€10,00€0,00
YAMAHA FX€10,00€0,00
BSS , KT EQ 2X31b€10,00€0,00
TOTAL€0,00
MICROPHONES QTYPRICE PER DAYPCS
AUDIX D61€10,000
SHURE B526€5,000
SHURE B5610€5,000
SHURE B5719€7,000
SHURE B5817€7,000
SHURE SM5719€5,000
SHURE SM 589€5,000
SHURE SM 58 ON/OFF2€5,000
SENNHEISER E6043€7,000
SENNHEISER E9025€7,000
SENNHEISER E90425€7,000
SENNHEISER E9055€7,000
SENNHEISER E90615€7,000
SENNHEISER E 835 S ON/OFF10€7,000
SENNHEISER MD 4211€10,000
SENNHEISER MD 4411€10,000
ELECTRO VOICE RE 3207€10,000
ELECTRO VOICE CO91€5,000
ELECTRO VOICE ND308B1€5,000
BEYER DYNAMIC M88TG1€10,000
AKG D1126€5,000
YAMAHA SUB KICK - SKRM 1001€10,000
ON/OFF TB MIC'S8€3,000
0
0
0
HEADSETSQTYPRICE PER DAYPCS
DPA 40662€20,00€0,00
DPA 40888€20,000
DPA 42882€20,000
DPA 606616€20,000
DPA D:FINE1€20,000
SHURE WH30 (black)2€10,000
SHURE WH 201€10,000
SHURE B545€10,00€0,00
SHURE WCL 16 (black)1€10,00€0,00
€0,00
0
TOTAL€0,00


LIGHTS is

MOVING LITESQTYPRICE P/DPCS
ROBE BMFL WASH BEAM20€150,00#####
CLAY PAKY MYTHOS -2 62€100,00#####
CLAY PAKY SHARPY16€50,00#####
CLAY PAKY HPE 1500 SPOT32€80,00#####
CLAY PAKY ALPHA PROFILE 150032€100,00#####
CLAY PAKY ALPHA PROFILE 120012€60,00#####
CLAY PAKY ALPHA WASH 1200 (plus 16 off)100€50,00#####
CLAY PAKY ALPHA WASH 5758€40,00#####
MARTIN MAC 2K PROFILE30€40,00#####
MARTIN MAC 2K new GOBO36€50,00#####
MARTIN MAC 2K PERFORMANCE28€50,00#####
MARTIN MAC 2K WASH33€40,00#####
FOS BEAM TITAN 40€35,00#####
FOS BEAM 7R 83€25,00#####
ETC REVOLUTION S44€50,00#####
NOVALIGHT HIGH GROUND16€75,00#####
NOVALIGHT FLOWER2€75,00#####
LED FIXTURESQTYPRICE P/DPCS
ROBE SPIDER56€75,00#####
ROBE LED BEAM 15032€35,00#####
GLP X4 BAR 2012€80,00#####
ETC LED S4 LUSTR2 20€60,00#####
PORTMAN P-116€60,00#####
CHAUVET FREEDOM PAR HEX 440€12,50#####
FOS WASH Q19 HP24€30,00#####
FOS HELIX15€25,00#####
FOS F596€25,00#####
FOS LED BAR 18X10W PRO99€20,00#####
FOS LED PAR BATTERY81€8,00#####
FOS LED PAR 18X10W PRO IP6548€10,00#####
FOS LED PAR UV12€10,00#####
FOS PIXEL LED 7X7W WW24€15,00#####
AMERICAN DJ INNO COLOR BEAM38€20,00#####
AMERICAN DJ PAR 64B LED PRO56€5,00#####
AMERICAN DJ MATRIX LED BEAM11€20,00#####
ELATION E LED TRI 64S59€5,00#####
LEADER LIGHT PRO PIXEL 1238€15,00#####
LIGHTING DESKSQTYPRICE P/DPCS
grand ma 2 light2€400,000
grand ma 2 on pc3€200,000
grand ma FULL SIZE 1€200,000
hes WHOLE HOG iii5€200,000
hes ROAD HOG 33€150,00#####
hes ROAD HOG 42€250,00#####
hes DMX WIDGET HOG 42€50,00#####
hes PLAYBACK WING - PROGRAMMER5 , 2€50,00#####
Hes DMX WIDGET HOG 31€50,00#####
HES hoglet 4 1€150,000
Avolite PEARL 20042€100,000
DMX 24CH. 3€30,000
FOLLOW SPOTSQTYPRICE P/DPCS
lycian M2 2,5kw(short,med,long)10€150,000
coemar TESTA 1.2kw6€70,000
thomas f/s chair4€50,000
DIMMERSQTYPRICE P/DPCS
grand ma dimmer rack 24ch.2€150,000
electron dimmer rack 24ch.10€120,000
electron dimmer rack 36ch.4€150,000
electron dimmer 6ch. X 3kw14€30,000
electron dimmer 6ch. X 5kw4€40,000
TOTAL€0,00


and the last one is PROFORMA that in this one I have to gather all the info from other two (audio & lights).

MANAGER
PRO-FORMA CLIENT
DESCRIPTIONAMOUNTPRICETOTAL
0,00 €
0,00 €
0,00 €
0,00 €
0,00 €
0,00 €
0,00 €
0,00 €
0,00 €
0,00 €
0,00 €
0,00 €
0,00 €
0,00 €
0,00 €
0,00 €
0,00 €
0,00 €
0,00 €
0,00 €
0,00 €
0,00 €
0,00 €
GRANT TOTAL0,00 €
VAT0,00 €
FINAL TOTAL0,00 €


At DESCRIPTION have to put the description of the equipment from AUDIO LIGHTS that are not 0 in Pcs, that PCS of this equipment and the PRICE.
Can you help? or give me a clue!
I dont know how to use VLOOKUP i with IF etc but I can learn real fast!

Thank you very much @etaf !
 
Upvote 0
are you making an Invoice - in the last sheet -
and the last one is PROFORMA that in this one I have to gather all the info from other two (audio & lights).
Click to expand...
Which will not be everything, but just the items purchased,
Gather ??????

you have a title category as well,

In the proforma , examples of how it is manually filled in would help

A vlookup can go and get the Price based on a description or even a part ID

but copying everything from 2 sheets - may need a bit more thought and WHAT version of Excel/operating system you have will help , also add to your profile
 
Upvote 0
etaf said:
are you making an Invoice - in the last sheet -

Which will not be everything, but just the items purchased,
Gather ??????

you have a title category as well,

In the proforma , examples of how it is manually filled in would help

A vlookup can go and get the Price based on a description or even a part ID

but copying everything from 2 sheets - may need a bit more thought and WHAT version of Excel/operating system you have will help , also add to your profile
Click to expand...
Hi etaf,

Yes I am trying to make an invoice. And sorry for my English I am from Greece, I knew gather is not the right word...
I am using Office 365 & Office 2013

What I am willing to do, is that
If , lets say from LIGHTS,
CLAY PAKY SHARPY15€50,00
has quantity then this item should be inserted to invoice with the item description ie
MANAGER
PRO-FORMACLIENT
DESCRIPTIONAMOUNTPRICETOTAL
CLAY PAKY SHARPY
15​
50​
750,00 €​
0,00 €​

so if an item from LIGHTS, AUDIO etc has quantity (pcs) it must be inserted to invoice automatically (quantity and description)
Thank you @etaf
 
Upvote 0
how does Excel know what description to lookup ?
in your example

CLAY PAKY SHARPY
has a stock of 16 Qty in the Lights sheet
But on the invoice they only want 15

Vlookup can get the price from the 2 tables based on the description

But it cant put a description in , if it has nothing to reference

Can you give an example with say 4 items on audio and 4 items on lights sheet
BUT then on the invoice show whats needed and why ?
using a smaller example
 
Upvote 0
etaf said:
how does Excel know what description to lookup ?
in your example

CLAY PAKY SHARPY
has a stock of 16 Qty in the Lights sheet
But on the invoice they only want 15

Vlookup can get the price from the 2 tables based on the description

But it cant put a description in , if it has nothing to reference

Can you give an example with say 4 items on audio and 4 items on lights sheet
BUT then on the invoice show whats needed and why ?
using a smaller example
Click to expand...
Yes of course I can do that. The idea is that excel to always check if the quantity of a product has changed, then insert that product, descriptin, Quantity and price to invoice. I will make a smaller example and post it.

Thank you very much for your effort!
 
Upvote 0
pantakos said:
Yes of course I can do that. The idea is that excel to always check if the quantity of a product has changed, then insert that product, descriptin, Quantity and price to invoice. I will make a smaller example and post it.

Thank you very much for your effort!
Click to expand...
I dont know if this can be done with vba ...
 
Upvote 0
probably , but i have not used VBA since the 90's , so don't answer VBA questions, i think your example with various senarios and expected results with explanation as why , may help a lot to decide if VBA is the best solution
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question
Return a user specified number of cell values to a comma list randomly
Replies
1
Views
75
Eric W
Eric W
T
  • Solved
Using IFNA with 2 Nested VLOOKUPs
Replies
5
Views
291
SanjayGMusafir
SanjayGMusafir
L
  • Question
Indirect Formula
Replies
5
Views
137
etaf
etaf
F
  • Question
Formula to insert a picture based on various cell values, w/out VBA, Macros, etc.
Replies
4
Views
117
Domenic
D
M
  • Solved
PDF creation of different sheet based on column value.
Replies
2
Views
264
mummbles74
M

Forum statistics

Threads
1,215,002
Messages
6,122,652
Members
449,092
Latest member
peppernaut

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top