|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
February 14th, 2006, 10:43 AM
posted to microsoft.public.excel.misc
|
|||
|
|||
Multiple VLookups - Can anyone help me please?
Hi all Does anybody know how the formula for a multiple vlookup. I want to lookup against a pivot table that has various customers in column B and product categories in column H and a sales value in column I. What I want to do is lookup a particular customer and a particular product and return the sales value. EG VLOOKUP (Customer A) and VLOOKUP (Product B) and return the sales value. Can anyone help??? Driving me mad!! Many thanks for looking. Regards Mark  -- certain_death ------------------------------------------------------------------------ certain_death's Profile: http://www.excelforum.com/member.php...o&userid=24561 View this thread: http://www.excelforum.com/showthread...hreadid=512174 
|
|
#2
February 14th, 2006, 11:02 AM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Multiple VLookups - Can anyone help me please?
One way is to put something like, in say J2:
=INDEX($I$2:$I$10,MATCH(1,($B$2:$B$10="Customer A")*($H$2:$H$10="Product B"),0)) then array-enter the formula, i.e. press CTRL+SHIFT+ENTER instead of just pressing ENTER Adapt the ranges to suit .. (note that entire col ranges cannot be used, eg: B:B, H:H, etc) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "certain_death" wrote in message news:certain_death.237tkz_1139913901.5555@excelfor um-nospam.com... Hi all Does anybody know how the formula for a multiple vlookup. I want to lookup against a pivot table that has various customers in column B and product categories in column H and a sales value in column I. What I want to do is lookup a particular customer and a particular product and return the sales value. EG VLOOKUP (Customer A) and VLOOKUP (Product B) and return the sales value. Can anyone help??? Driving me mad!! Many thanks for looking. Regards Mark -- certain_death ------------------------------------------------------------------------ certain_death's Profile: http://www.excelforum.com/member.php...o&userid=24561 View this thread: http://www.excelforum.com/showthread...hreadid=512174
|
|
#3
February 14th, 2006, 11:20 AM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Multiple VLookups - Can anyone help me please?
Hi Max Have tried this but am getting the classic #N/A response. What am I doing wrong? Here is my formula =INDEX('Sales and GC'!$I$7:$I$65000,MATCH(1,('Sales and GC'!$B$7:$B$65000="BOOTS")*('Sales and GC'!$H$7:$H$65000="LIL DIG"),0)) Anything I'm doing wrong? Thanks Mark -- certain_death ------------------------------------------------------------------------ certain_death's Profile: http://www.excelforum.com/member.php...o&userid=24561 View this thread: http://www.excelforum.com/showthread...hreadid=512174
|
|
#4
February 14th, 2006, 12:04 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Multiple VLookups - Can anyone help me please?
Hi Mark,
Have a look at the GETPIVOTDATA excel function rgds André "certain_death" wrote in message news:certain_death.237tkz_1139913901.5555@excelfor um-nospam.com... Hi all Does anybody know how the formula for a multiple vlookup. I want to lookup against a pivot table that has various customers in column B and product categories in column H and a sales value in column I. What I want to do is lookup a particular customer and a particular product and return the sales value. EG VLOOKUP (Customer A) and VLOOKUP (Product B) and return the sales value. Can anyone help??? Driving me mad!! Many thanks for looking. Regards Mark -- certain_death ------------------------------------------------------------------------ certain_death's Profile: http://www.excelforum.com/member.php...o&userid=24561 View this thread: http://www.excelforum.com/showthread...hreadid=512174
|
|
#5
February 14th, 2006, 03:10 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Multiple VLookups - Can anyone help me please?
I'd check the data first.
Do you have Boots in B7:B65000 and do you have LIL DIG in H7:H65000 of that same row with Boots in it? certain_death wrote: Hi Max Have tried this but am getting the classic #N/A response. What am I doing wrong? Here is my formula =INDEX('Sales and GC'!$I$7:$I$65000,MATCH(1,('Sales and GC'!$B$7:$B$65000="BOOTS")*('Sales and GC'!$H$7:$H$65000="LIL DIG"),0)) Anything I'm doing wrong? Thanks Mark -- certain_death ------------------------------------------------------------------------ certain_death's Profile: http://www.excelforum.com/member.php...o&userid=24561 View this thread: http://www.excelforum.com/showthread...hreadid=512174 -- Dave Peterson
|
|
#6
February 14th, 2006, 06:42 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Multiple VLookups - Can anyone help me please?
Think there's nothing wrong with your adaptation. Assuming there should be
a match/result for the inputs: BOOTS/LIL DIG, then probably the source data may contain extraneous white spaces (leading, trailing or in-between spaces) which is throwing the matching off (These spaces are not readily visible). We could use TRIM around cols B and H to make the matching more robust: =INDEX('Sales and GC'!$I$7:$I$65000,MATCH(1,(TRIM('Sales and GC'!$B$7:$B$65000)="BOOTS")*(TRIM('Sales and GC'!$H$7:$H$65000)="LIL DIG"),0)) (Above array-entered as before) P/s: Try using the smallest range large enough to cover, for calc efficiency/performance. Your range is pretty large g. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "certain_death" wrote in message news:certain_death.237vfm_1139916301.0022@excelfor um-nospam.com... Hi Max Have tried this but am getting the classic #N/A response. What am I doing wrong? Here is my formula =INDEX('Sales and GC'!$I$7:$I$65000,MATCH(1,('Sales and GC'!$B$7:$B$65000="BOOTS")*('Sales and GC'!$H$7:$H$65000="LIL DIG"),0)) Anything I'm doing wrong? Thanks Mark -- certain_death ------------------------------------------------------------------------ certain_death's Profile: http://www.excelforum.com/member.php...o&userid=24561 View this thread: http://www.excelforum.com/showthread...hreadid=512174
|
| Thread Tools | |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Thread Starter | Forum | Replies | Last Post |
| Sum a row of multiple vlookups | jtgostars | Worksheet Functions | 1 | December 10th, 2005 06:37 PM |
| Multiple Vlookups | shaj | Worksheet Functions | 2 | November 18th, 2005 06:32 PM |
| multiple vlookups | inthestands | Worksheet Functions | 0 | January 13th, 2005 06:35 PM |
| VLOOKUP's Across Multiple Sheets | Nathan Horrigan | Worksheet Functions | 6 | January 11th, 2004 01:09 AM |
| Multiple values in a single cell, and validation | Harlan Grove | Worksheet Functions | 1 | December 5th, 2003 07:58 PM |
Linear Mode
