YTLE#67: VLOOKUP & DGET functions

A B C
1 Date Date Answer
2 >=1/2/11 =1/2/11

I have a table with three columns. Number, Item, and Location.

I need to create a table where Location is the X axis, Item is on the Y axis, and Number is filled in automatically when the two meet.

For example:

114 Basil REIGHT
129 Basil RFIVE
144 Basil RFOUR
159 Basil RONE
174 Basil RSEVEN
189 Basil RSIX
204 Basil RTHREE
219 Basil RTWO

I want the table to look like this:
______R1___R2___R3___R4___R5___R6___R7___R8
Basil__159__219__204__144__129__189__174__114

I know a Pivot Table would be easiest, but unfortunately, I need this to auto-update any time the data is changed, so I need to use a formula.

Any ideas? I think I need to use DGET or VLOOKUP, but I'm just not too sure.

Thanks for your time!!

I am wanting to replace some VLookup formulas with the DGet function but cannot get the DGet to work. The syntax seems simple enough but all I get is #N/A errors. The criteria exist in the database range, are not duplicated and I am trying to retrieve the value from the second column of the database range as such:

=DGET(E6:F500,2,I2:I500)

A possible complexity is that the database range is actually part of a pivot table and that I cannot use headings for the columns, or is it simply that you cannot use DGET when your criteria is a long list rather than just a heading and one criteria value?

Any suggestions would be appreciated.

I'm note sure what to use here.

******** ******************** ************************************************************************> Microsoft Excel - Book1 ___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout A1 =
A B C D E F G H I 1 Date Qty Run_No Status Weight     Run_No Date 2 1/31/08 5 1 x       2 1/1/08 3 2/15/08 6 3         3 Open 4 1/1/08 15 2 x R     1 2/28/08 5 3/17/08 18 2             6 2/28/08 22 1 w R         7 1/1/08 99 3 x           8 2/15/08 75 1 e           Sheet1  
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

I need a formula in I2 thru I4 that will find an "R" in the weight column and return the appropriate date for the certain run_no. If there is no ""R" then return Open

Thanks,

Ross

Hi,
I have a database with values in this form;
Mass Intensity
10 123
11 69
12 789
13 566
14 8
15 158
... ...

and i want to search using another list of expected massesm to return the intensity of only the desired values. For instance;
Mass Intensity
12.1 789
13.9 8
...
so essentially i am using list2 to extract intensity values from list1. the problem is that the values don't match but are within a small range of the expected values say 0.2. vLookup won't work since it will match only the next smallest value and I can't seem to figure out the right criteria for dGet or if it will indeed work for this application.

Thanks,
Simon

I'm trying to pull data from a worksheet when the name of the tab equals the name is a cell. Can anyone help?

I have a range of data where I need to find the value in a column by narrowing down with two different criteria.

These are my column titles:
Account Number
Year
January
February
March
etc.

I need to lookup down the account number column and return the value for March where the Year is 2005. In other words, on another spreadsheet, I have a list of accounts and I'm trying to search my data range and find the March value where the year is 2005.

I'm not sure how to combine DGET, VLOOKUP, MATCH, and/or INDEX to accomplish this.

Thanks for your help.

Hi Guys,

So I have been looking for a few days for a solution to this - maybe you can help.

In sheet 2, I need to populate Buyer Number by referencing the two values (BU, Item#)that appear on Sheet 1 and Sheet 2.

The issue that I was having with an Index/Match was that it wasnt helping for multiple values.

For example, the same item number might exists at 5 different Business Units, each with its own Buyer Number. It might exists twice under the same BU and same Buyer.

The location field can be ignored.

Sheet2
Business Unit Item Number Location Buyer Number
1200 140229 QA
1020 151101 MR
1200 210329 MR
1020 219101 QA
1020 275101 QA
1600 168171003 QA
1020 168171003 MR
1000 168171003 MR
1200 168171003 QA
1300 168171003 QA
1020 168175001 MR
1000 168175001 QA
1000 168183001 MR
1600 168183001 MR
1000 168199001 MR
1000 M934840A003 MR
1200 M934840A003 QA
1000 M934843A001 QA
1000 M934845A020 QA
1300 M934845A020 MR
1300 M934881A002 QA
1300 M934900A001 MR
1300 M934901A001 QA
1300 M934902A001 QA
1300 M934903A001 QA
1300 M934903A002 QA
1000 M935123A001 QA

Sheet1
Business Unit Item Number Buyer Number
1600 168171003 929114
1020 168171003 9048811
1000 168171003 903091
1200 168171003 9096328
1300 168171003 9039276
1020 168175001 9048811
1000 168175001 909394
1000 168183001 978412
1600 168183001 929114
1000 168199001 909394
1000 M934840A003 904415
1200 M934840A003 954831
1000 M934843A001 9048811
1000 M934845A020 905810
1300 M934845A020 9097971
1300 M934881A002 9091989
1300 M934900A001 9039276
1300 M934901A001 9039276
1300 M934902A001 9039276
1300 M934903A001 9039276
1300 M934903A002 9039276
1000 M935123A001 9090771
1200 140229 9096328
1020 151101 905810
1200 210329 9098959
1020 219101 9091065
1020 275101 9091065



Any ideas?

Thanks.

Also, it would be great if this could deal with a few blank values in buyer number

Hi Guys,

So I have been looking for a few days for a solution to this - maybe you can help.

In sheet 2, I need to populate Buyer Number by referencing the two values (BU, Item#)that appear on Sheet 1 and Sheet 2.

The issue that I was having with an Index/Match was that it wasnt helping for multiple values.

For example, the same item number might exists at 5 different Business Units, each with its own Buyer Number. It might exists twice under the same BU and same Buyer.

The location field can be ignored.

Sheet2
Business Unit Item Number Location Buyer Number 1200 140229 QA 1020 151101 MR 1200 210329 MR 1020 219101 QA 1020 275101 QA 1600 168171003 QA 1020 168171003 MR 1000 168171003 MR 1200 168171003 QA 1300 168171003 QA 1020 168175001 MR 1000 168175001 QA 1000 168183001 MR 1600 168183001 MR 1000 168199001 MR 1000 M934840A003 MR 1200 M934840A003 QA 1000 M934843A001 QA 1000 M934845A020 QA 1300 M934845A020 MR 1300 M934881A002 QA 1300 M934900A001 MR 1300 M934901A001 QA 1300 M934902A001 QA 1300 M934903A001 QA 1300 M934903A002 QA 1000 M935123A001 QA
Sheet1
Business Unit Item Number Buyer Number 1600 168171003 929114 1020 168171003 9048811 1000 168171003 903091 1200 168171003 9096328 1300 168171003 9039276 1020 168175001 9048811 1000 168175001 909394 1000 168183001 978412 1600 168183001 929114 1000 168199001 909394 1000 M934840A003 904415 1200 M934840A003 954831 1000 M934843A001 9048811 1000 M934845A020 905810 1300 M934845A020 9097971 1300 M934881A002 9091989 1300 M934900A001 9039276 1300 M934901A001 9039276 1300 M934902A001 9039276 1300 M934903A001 9039276 1300 M934903A002 9039276 1000 M935123A001 9090771 1200 140229 9096328 1020 151101 905810 1200 210329 9098959 1020 219101 9091065 1020 275101 9091065


Any ideas?

Thanks.

Also, it would be great if this could deal with a few blank values in buyer number