Index(Match Problem

Hi guys,

Wondering if anyone can point me in the right direction please....

Problem:

I have item numbers listed in column "E"
these items are located @ "A1" dropdown menu Venue1 & Venue2
In column G I have the formula as =IF(A1="Venue1",VLOOKUP(E2,A10:B18,2,FALSE)),IF(A1="Venue2",VLOOKUP(E2,A1:B9,2,FALSE))
which is meant to check on the sales percentages as per each section.



I keep getting an error, the attached spreadsheet should narrate it better if my dribble has not lol,
What is it I'm not understanding or doing wrong within the formula or cannot even be done?

Using eXcel'16

Any help,
Much appreciated,
Chad

Try this in G2 copied down:

=IF(A$1="Venue1",VLOOKUP(E2,$A$2:$B$9,2,FALSE),IF(A$1="Venue2",VLOOKUP(E2,$A$11:$B$18,2,FALSE),""))

1) Your data ranges are in the wrong order.
IF A1="Venue1" then you want the range from A2:B9 not A10:B18
Same goes for IFA1="Venue2"

2) The IFs are NOT nested. Youve ended the first IF before the second one starts hence the VALUE error,
It should be

=IF(A1="Venue1",VLOOKUP(E2,A11:B18,2,FALSE),IF(A1="Venue2",VLOOKUP(E2,A2:B9,2,FALSE)))
or given only those two venues
=IF(A1="Venue1",VLOOKUP(E2,A11:B18,2,FALSE),VLOOKUP(E2,A2:B9,2,FALSE))

or even
=VLOOKUP(E2,INDIRECT(LOOKUP(A1,{"Venue1","Venue2"},{"A2:B9","A11:B18"})),2,FALSE)

No. You would change the layout of your source data, adding a column for the venue, and then you would do INDEX MATCH MATCH using the venue in A1 as a match criterion.

with a pivot table.

see the attached file.

You'd lay it out like this:

Excel 2016 (Windows) 32 bit

	A	B	C	D	E	F	G	H
1		Venue2				Item		Sales
2	Venue1	1	12%			1		16%
3	Venue1	2	14%			8		11%
4	Venue1	3	13%			7		12%
5	Venue1	4	11%			6		14%
6	Venue1	5	10%
7	Venue1	6	11%
8	Venue1	7	12%
9	Venue1	8	17%
10
11	Venue2	1	16%
12	Venue2	2	9%
13	Venue2	3	10%
14	Venue2	4	12%
15	Venue2	5	15%
16	Venue2	6	14%
17	Venue2	7	12%
18	Venue2	8	11%

Sheet: Sheet1

and then use this formula:

Excel 2016 (Windows) 32 bit

	H
2	=SUMPRODUCT(($A$2:$A1000=$B$1)*($B$2:$B1000=F2),$C$2:$C1000)

Sheet: Sheet1