Pull Data From One Excel Sheet To Another Based on Lookup Values

%3CLINGO-SUB%20id%3D%22lingo-sub-2999783%22%20slang%3D%22en-US%22%3EPull%20Data%20From%20One%20Excel%20Sheet%20To%20Another%20Based%20on%20Lookup%20Values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2999783%22%20slang%3D%22en-US%22%3E%3CP%20class%3D%22%22%3EOkay%20so%20let%20me%20preface%2C%20I%20am%20trying%20to%20make%20an%20excel%20spread%20sheet%20to%20track%20my%20option%20investments.%20To%20do%20so%20I%20would%20like%20to%20simply%20import%20my%20options%20data%3A%20stock%20symbol%2C%20prices%2C%20commissions%2C%20etc.%20Into%20an%20individual%20tab.%20I%20would%20then%20like%20to%20be%20able%20to%20use%20a%20sheet%20used%20a%20%22search%22%20tab%20where%20I%20could%20just%20punch%20in%20%22AAL%22%20and%20all%20transactions%20in%20my%20data%20sheet%20(be%20in%201%20or%20100)%20containing%20AAL%20would%20populate%20on%20the%20various%20data%20points%20I%20have%20in%20my%20data%20tab%20which%20I%20can%20then%20use%20for%20profitability%20calculations%2C%20etc.%3C%2FP%3E%3CP%20class%3D%22%22%3E%26nbsp%3B%3C%2FP%3E%3CP%20class%3D%22%22%3EHow%20could%20I%20go%20about%20creating%20this%20type%20of%20sheet%3F%20I%20have%20been%20trying%20to%20use%20%22VLOOKUP%22%20but%20I%20am%20only%20able%20to%20get%20a%20singular%20occurrence%2C%20not%20populate%20all%20occurrences%20of%20said%20data%20points.%20Ex.%20%3DVLOOKUP(L2%2CA1%3AI33%2C7%2C)%20it%20will%20return%20the%20first%20%24.54.%20But%20I%20am%20not%20sure%20how%20to%20return%20any%20other%20of%20the%203%20trades%20data.%3C%2FP%3E%3CP%20class%3D%22%22%3E%26nbsp%3B%3C%2FP%3E%3CP%20class%3D%22%22%3EBelow%20is%20an%20example%20of%20the%20imported%20data%20that%20would%20be%20inside%20the%20data%20sheet%20that%20I%20would%20like%20to%20see%20popular%20when%20when%20I%20look%20up%20that%20AAL%20symbol.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2999783%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-2999931%22%20slang%3D%22en-US%22%3ERe%3A%20Pull%20Data%20From%20One%20Excel%20Sheet%20To%20Another%20Based%20on%20Lookup%20Values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2999931%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1228125%22%20target%3D%22_blank%22%3E%40Oxley123%3C%2FA%3E%26nbsp%3BIf%20you%20are%20on%20MS365%20or%20Excel%202021%2C%20use%20the%20FILTER%20function%20(Cell%20C2).%20If%20that's%20not%20an%20option%2C%20you'll%20need%20to%20revert%20to%20a%20more%20cumbersome%20formulae%20(Columns%20E%3AF).%20Both%20are%20demonstrated%20in%20the%20attached%20file.%20I've%20used%20named%20ranges%20to%20make%20the%20formulae%20easier%20to%20write%2C%20read%20and%20maintain.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAlternatively%2C%20why%20not%20forget%20about%20the%20Search%20tab%20all-together%3F%20Do%20the%20search%20by%20filtering%20the%20desired%20symbol%2C%20directly%20in%20the%20transactions%20table.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

Okay so let me preface, I am trying to make an excel spread sheet to track my option investments. To do so I would like to simply import my options data: stock symbol, prices, commissions, etc. Into an individual tab. I would then like to be able to use a sheet used a "search" tab where I could just punch in "AAL" and all transactions in my data sheet (be in 1 or 100) containing AAL would populate on the various data points I have in my data tab which I can then use for profitability calculations, etc.

 

How could I go about creating this type of sheet? I have been trying to use "VLOOKUP" but I am only able to get a singular occurrence, not populate all occurrences of said data points. Ex. =VLOOKUP(L2,A1:I33,7,) it will return the first $.54. But I am not sure how to return any other of the 3 trades data.

 

Below is an example of the imported data that would be inside the data sheet that I would like to see popular when when I look up that AAL symbol.

1 Reply

@Oxley123 If you are on MS365 or Excel 2021, use the FILTER function (Cell C2). If that's not an option, you'll need to revert to a more cumbersome formulae (Columns E:F). Both are demonstrated in the attached file. I've used named ranges to make the formulae easier to write, read and maintain. 

 

Alternatively, why not forget about the Search tab all-together? Do the search by filtering the desired symbol, directly in the transactions table.