Multiple VLookups - Can anyone help me please?

[certain_death]

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

[Max]

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" <[email protected]>
wrote in message
news:[email protected]...
>
> 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
>

[certain_death]

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

[Andre Croteau]

Hi Mark,

Have a look at the GETPIVOTDATA excel function

rgds

André

"certain_death" <[email protected]>
wrote in message
news:[email protected]...
>
> 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
>

[Dave Peterson]

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

[Max]

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" <[email protected]>
wrote in message
news:[email protected]...
>
> 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
>

[tammanabhagavan]

This may be help ful

Data:
Table Supplier Product Cases
aProd1 a Prod1 1
aProd2 a Prod2 2
bProd1 b Prod1 3
bProd2 b Prod2 4
cProd1 c Prod1 5
cProd2 c Prod2 6


Column Table = Cell relating to Supplier & Cell relating to Product ( Ex:=B12&C12)

Result

Supplier Prod1 Prod2
a 1 2
b 3 4
c 5 6

Formula used:

=VLOOKUP($A2&B$1,$A$12:$D$17,4,FALSE)

Visit the following link for further clarification:

http://www.viloria.com/secondthought.../00000756.html

[TimPhillips]

Hi, I have a slightly different question, i think.

How do i get a vlookup to count multiple values?

I have a spreadsheet with suppliers booking in times, i have a vlookup formula in place to find out whether they were on time. But i can't get it to look for multiple deliveries.

Any Ideas?

Tim

[certain_death]

Hi Tim

Here is a multiple lookup formula...

Just replace your ranges

Hope this helps.

If you have trouble post your data and I'll have a look.

You may also want to have a look at INDEX MATCH and SUMPRODUCT formulas as they can be helpful for this type of LOOKUP.

This formula is saying "lookup where Sheet1'!$B$2:$B$408=G3 and Adv P&L codes.xls]Sheet1'!$C$2:$C$408=H3 and return [Adv P&L codes.xls]Sheet1'!$D$2:$D$408)

=LOOKUP(2,1/(([Adv P&L codes.xls]Sheet1'!$B$2:$B$408=G3)*([Adv P&L codes.xls]Sheet1'!$C$2:$C$408=H3)),[Adv P&L codes.xls]Sheet1'!$D$2:$D$408)

Thanks
Mark