Home

Help please

%3CLINGO-SUB%20id%3D%22lingo-sub-814580%22%20slang%3D%22en-US%22%3EHelp%20please%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-814580%22%20slang%3D%22en-US%22%3EI%20have%20two%20worksheets.%20Let%E2%80%99s%20say%20A%20%26amp%3B%20B%2C%20on%20work%20sheet%20b%20there%20is%20a%20column%20with%20a%20list%20of%20products%20and%20beside%20it%20a%20column%20where%20you%20enter%20the%20qty%20of%20the%20product%20you%20want.%20On%20work%20sheet%20A%20I%20want%20the%20column%20where%20product%20description%20goes%20to%20automatically%20pull%20data%20from%20the%20list%20of%20products%20on%20worksheet%20b%20if%20that%20product%20has%20a%20number%20entered%20in%20the%20qty%20column%20beside%20it.%20I%20tried%20using%20the%20ifs%20function%20but%20I%20have%20too%20many%20products.%20Does%20anyone%20know%20of%20a%20way%20to%20do%20this%3F%3CBR%20%2F%3EThanks%20heaps%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-814580%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-814727%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20please%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-814727%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F395829%22%20target%3D%22_blank%22%3E%40Aleisha2125%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPerhaps%20VLOOKUP%20or%20INDEX%2FMATCH.%20Could%20you%20submit%20small%20sample%20file%20to%20illustrate%20how%20data%20is%20organised%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-816172%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20please%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-816172%22%20slang%3D%22en-US%22%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%26nbsp%3B%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20replying%20I%20have%20attached%20a%20sample%20file%20for%20you%20to%20have%20a%20look%20at.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-818638%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20please%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-818638%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F395829%22%20target%3D%22_blank%22%3E%40Aleisha2125%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHere%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20445px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F128090i2EA6A21F152BEAF1%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Ein%20D5%20you%20may%20enter%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DIFERROR(%0A%20%20%20INDEX('Worksheet%20B'!%24C%3A%24C%2C%0A%20%20%20%20%20%20AGGREGATE(15%2C6%2C%0A%20%20%20%20%20%20%20%20%201%2F('Worksheet%20B'!%24E%243%3A%24E%24100%26lt%3B%26gt%3B0)*ROW(%24A%243%3A%24A%24100)%2C%0A%20%20%20%20%20%20%20%20%20ROW()-ROW(%24D%244))%0A%20%20%20)%2C%0A%22%22)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Eand%20drag%20it%20down%20till%20empty%20cells%20appear.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-818677%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20please%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-818677%22%20slang%3D%22en-US%22%3E%3CP%3EThanks%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B!!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20work%20perfectly%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-820856%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20please%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-820856%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F395829%22%20target%3D%22_blank%22%3E%40Aleisha2125%3C%2FA%3E%26nbsp%3B%2C%20you%20are%20welcome%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Aleisha2125
New Contributor
I have two worksheets. Let’s say A & B, on work sheet b there is a column with a list of products and beside it a column where you enter the qty of the product you want. On work sheet A I want the column where product description goes to automatically pull data from the list of products on worksheet b if that product has a number entered in the qty column beside it. I tried using the ifs function but I have too many products. Does anyone know of a way to do this?
Thanks heaps
5 Replies

@Aleisha2125 

Perhaps VLOOKUP or INDEX/MATCH. Could you submit small sample file to illustrate how data is organised?

 

 

Hi  @Sergei Baklan 

 

Thanks for replying I have attached a sample file for you to have a look at.

@Aleisha2125 

Here

image.png

in D5 you may enter

=IFERROR(
   INDEX('Worksheet B'!$C:$C,
      AGGREGATE(15,6,
         1/('Worksheet B'!$E$3:$E$100<>0)*ROW($A$3:$A$100),
         ROW()-ROW($D$4))
   ),
"")

and drag it down till empty cells appear. 

Thanks @Sergei Baklan !!

 

This work perfectly

@Aleisha2125 , you are welcome

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
48 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
30 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies