Tuesday, April 9, 2013

yogi_Pull Row By Row Records From Another Sheet Using Multiple Criteria Across Multiple Sheets

                                          Google Spreadsheet   Post  #1101
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Apr 9, 2013
user Jared Morgan :(http://productforums.google.com/forum/?zx=dp8talaivacu#!category-topic/docs/spreadsheets/EmJJ7df_ifo)
Multiple Lookup and Get Function
Hopefully this will make sense and with the document even more so.  I'm trying to populate projects!G:Y so that it grabs the appropriate (sometimes multiple) assets!D (loc) based on matching assets!BB:BL (PIDs) with projects!A and assets!E (asset_type) with projects!1.  

I haven't been able to figure it out with attempts using IF and VLOOKUP, but I'm also new to gDocs and rusty on my functions overall.  I think this is close, but it doesn't return multiples and seems to be grabbing the wrong raw from what I can tell: =IF((asset_type="RT"),(VLOOKUP(A2,assets,1)),FALSE)

Here's my working sheet.

Hey Yogi,

Thanks for your help Yogi & Hyde.

a) I'm not sure what formula will work best here.  I was trying to do it with a nested vlookup and if formula, but could not get correct results and mostly kept getting errors.  I'll use a specific example as so that hopefully it is clear.  If you look at projects!G3 the results are coming from assets!D4 by matching two arrays assets!E:E and assets!BB:BL with projects!G1 and projects!A3 respectively.  An example of multiple results can be seen in projects!I3.  The formula works the same for each column with a different "Asset Type."

b) I want the results in projects!G:Y

c) I copied over the results so you can see what I'm trying to get for this small sample.  (The actual working document has several thousand rows of data.)

Let me know if that's still not enough information or unclear.

Thanks again,
following is a solution to the problem