help with Vlook up or function to pull data from excel database

**gullit** · 01-22-2015, 01:29 PM

Hello
I need your help with a little project. i have included a sample in the attachment.
what i am trying to do is a look up of function option that can provide the results based on the data entered.

** the attachment is only part of the data base (too many to enter the full file)
what i need to do:
in a different sheet number i will have the following options to narrow the search

* Dest. City
* Service Level
* kg. rate

the results must be as on the first tab on the attachment.
I have considered filters but i want to make this as simple as possible, any help will be greatly appreciated.
as always this forum rocks!

**daffodil11** · 01-22-2015, 01:39 PM

I'm not sure I understand all of your criteria. What is >100 KGS including or excluding?

Also, a number of your fields are broken link. Can you provide what the actual output should be given the criteria in the example, or describe how >100 KGs applied to the result given?

I've attached an example using INDEX(..SMALL(IF using only the first two criteria.

**Glenn Kennedy** · 01-22-2015, 01:43 PM

Hi try this... It's an Array formula, ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

Change the parameters in D4/D6 (merged cells - ugh!! don't use 'em - they'll cause you grief) and watch...

**Glenn Kennedy** · 01-22-2015, 01:44 PM

For the record... I just ignored the >100 kgs, as the desired result line included a wide range of weights. Let me know if that was wrong.

**gullit** · 01-22-2015, 02:12 PM

hello
thanks for the help, all those examples does what i intended to an extent. The point is that this is only a small portion of the data base and the rest of the data contains different airlines that go to the same destination.
for example i could have 5 or more airlien that goes to destination city MEX and the date will need to be pulled from the data base in order to compare prices.
on regards to the Kgs, that was to see if we choose a kg it can pull the rates for that kg column only
kg used on the data base shows as below
Min. <45 Kgs >45 Kgs >100 Kgs >300 Kgs >500 Kgs >1000 Kgs

**daffodil11** · 01-22-2015, 02:22 PM

Sure. I just removed the fields we don't need, and set an OFFSET to the Min column for a match of the field.

I added more MEX to the data so you can see how it dynamically pulls all rows that apply.

**Glenn Kennedy** · 01-22-2015, 02:22 PM

You didn't say that. the implication that I took from this was that it had to match MEX and GCR (and maybe weight, too).

Where to from here?? How else do you need this tweaked (at least we're getting multiple results now!!)

**Glenn Kennedy** · 01-22-2015, 02:23 PM

Daffodil 11, Having just read your comments in the attachment - you need to get out more!!

**gullit** · 01-22-2015, 02:37 PM

Hi

the variables that will be taken into consideration are destination, service level and kg.
the return result must be the combination of all these 3
example: destination : GDL , service level: GCR , kg: <100 , the results will be for all airlines that meet the criteria.
Daffodil 11: somehow i am unable to open the attachment.
Glenn: yes, we are able to see more multiple results now but the service level is still pulling all options and not just GCR.
also i noticed that you added the formulas to multiples rows, but we dont really know how many rows we will have for the search, it could be between 1 and 20 (or more), how can we just visualize the rows that have just the info requested

**daffodil11** · 01-22-2015, 02:39 PM

Let's try this again!

**Glenn Kennedy** · 01-22-2015, 02:47 PM

OK, GCR and airport criteria are now in. I'm still not 100% clear what you need by way of weight, though.

The shaded areas (drag down frther if needed) will populaue only if/when there are matches; otherwise the cells will remain blank. Is that not what you need?

**Glenn Kennedy** · 01-22-2015, 02:49 PM

OK, Daffodil & myself are now returning the same thing; but neither of us are taking weight into account. The reason that my formula is much longer is that I have made mine return a blank if there's nothing in a cell (e.g. if the comments box is empty).

Please be a little more clear as to what you need.

**daffodil11** · 01-22-2015, 03:11 PM

In my example I deleted all but one of the KG columns, set H12 = E8, so that it dynamically only returns that single data point.

**Glenn Kennedy** · 01-22-2015, 03:17 PM

Sorry, you're right, I need new glasses...

**gullit** · 01-22-2015, 04:11 PM

The example sent by Daffodil11 does what needed. i just will have to add some conditional formating to the empty rows so it can add the color and borders when there is data present on the rows. hopefully that will help clean out the gray empty rows.
i will do this when i get off the office and see how it works.
Thanks a whole bunch!! you guys are great!!

**daffodil11** · 01-22-2015, 04:17 PM

No problem. I am glad we were both able to help you.

**gullit** · 01-22-2015, 05:31 PM

of course, my thanks are for both of you! great, great help.

help with Vlook up or function to pull data from excel database

LinkBack

Thread Tools

Rate This Thread

Display

help with Vlook up or function to pull data from excel database

Re: help with Vlook up or function to pull data from excel database

Re: help with Vlook up or function to pull data from excel database

Re: help with Vlook up or function to pull data from excel database

Re: help with Vlook up or function to pull data from excel database

Re: help with Vlook up or function to pull data from excel database

Re: help with Vlook up or function to pull data from excel database

Re: help with Vlook up or function to pull data from excel database

Re: help with Vlook up or function to pull data from excel database

Re: help with Vlook up or function to pull data from excel database

Re: help with Vlook up or function to pull data from excel database

Re: help with Vlook up or function to pull data from excel database

Re: help with Vlook up or function to pull data from excel database

Re: help with Vlook up or function to pull data from excel database

Re: help with Vlook up or function to pull data from excel database

Re: help with Vlook up or function to pull data from excel database

Re: help with Vlook up or function to pull data from excel database

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] How to restrict the voucher no. to A1234567 format and use vlook up function to link data?

Is there a way to pull data from a SQL Database into Excel automatically?

Is it possible? Refrencing an Access database to pull information to excel

Use of Excel to pull data from database daily, posting new data below older data

VBA Pull/Vlook up to same range on Dynamic amount of workbooks

Bookmarks

Bookmarks

Posting Permissions