# Index(Match Problem

1. ## 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,

2. ## Re: Index(Match Problem

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),""))

3. ## Re: Index(Match Problem

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)

4. ## Re: Index(Match Problem

Thank you Ali & Special-K for responding with both valid remedies,
an additional question, if I had say 50 venues would I have to continue adding venue after venue etc etc ?

ie:=IF(A1="Venue1",VLOOKUP(E2,A11:B18,2,FALSE),IF(A1="Venue2",VLOOKUP(E2,A2:B9,2,FALSE),IF(A1="Venue3",VLOOKUP(E2,A2:B9,2,FALSE))))

Cheers,

5. ## Re: Index(Match Problem

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.

7. ## Re: Index(Match Problem

How do you mean Ali, that lost me for sure...
There are 60 venues in total

8. ## Re: Index(Match Problem

with a pivot table.

see the attached file.

9. ## Re: Index(Match Problem

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

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1