Index & match from multiple tables

[bademployee]

Hi all,

Appreciate any help to solve below:-

Sheet1:

[/URL][/IMG]

B3 is a validation list to of company's.

B4 is another validation list of products, depending on what company is selected in B3.

Sheet2:

[/URL][/IMG]

On sheet1, I am trying to fill E4:I7 from data on sheet2, depending on what company is selected in B3 & what product is selected in B4.

In E4, I've come up with:

=INDEX('sheet2'!$A$1:$A$27,0,MATCH(B$4,'sheet2'!$A$1:$A$27)+MATCH($E$3,'sheet2'!B1:f1,0))

Not really sure what I'm doing.

Thanks in advance.

Mark

 

[Aladin Akyurek]

1) The images are not readable into Excel.

2) You should try to show the output that must obtain, not a non-working formula because the latter can be misleading about the intent.

 

[Simon4s]

Hi all,

Appreciate any help to solve below:-

Sheet1:

[/URL][/IMG]

B3 is a validation list to of company's.

B4 is another validation list of products, depending on what company is selected in B3.

Sheet2:

[/URL][/IMG]

On sheet1, I am trying to fill E4:I7 from data on sheet2, depending on what company is selected in B3 & what product is selected in B4.

In E4, I've come up with:

=INDEX('sheet2'!$A$1:$A$27,0,MATCH(B$4,'sheet2'!$A$1:$A$27)+MATCH($E$3,'sheet2'!B1:f1,0))

Not really sure what I'm doing.

Thanks in advance.

Mark

How many companies do you have? There's definitely a way to do it with index match... But a more organized way to do it:

1. Go to sheet 2
2. select A1:F7
3. right click
4. define name "Company1"
5. repeat for all companies
6. Go to sheet 1
7. in C4, type, "=vlookup($B4,indirect($b$3),2,false) <--- this does a Vlookup on product1 from the specified table in B3
8. click the thank button =D

 

[AhoyNC]

Maybe this link will help.
https://www.youtube.com/watch?v=WogFiO3aOas

 

[bosco_yip]

Try,

In "Sheet1" E4, formula copied across to I4 and all copied down :

=INDEX((Sheet2!$B$2:$F$7,Sheet2!$B$12:$F$17,Sheet2!$B$22:$F$27),MATCH($B4,CHOOSE(MATCH($B$3,{"Company1","Company2","Company3"},0),Sheet2!$A$2:$A$7,Sheet2!$A$12:$A$17,Sheet2!$A$22:$A$27),0),MATCH(E$3,Sheet2!$B$1:$F$1,0),MATCH($B$3,{"Company1","CoMpany2","Company3"},0))

Regards
Bosco

 

[bademployee]

Try,

In "Sheet1" E4, formula copied across to I4 and all copied down :

=INDEX((Sheet2!$B$2:$F$7,Sheet2!$B$12:$F$17,Sheet2!$B$22:$F$27),MATCH($B4,CHOOSE(MATCH($B$3,{"Company1","Company2","Company3"},0),Sheet2!$A$2:$A$7,Sheet2!$A$12:$A$17,Sheet2!$A$22:$A$27),0),MATCH(E$3,Sheet2!$B$1:$F$1,0),MATCH($B$3,{"Company1","CoMpany2","Company3"},0))

Regards
Bosco

Many thanks Bosco, your formula worked perfectly.

Cheers

Mark

 

[bademployee]

How many companies do you have? There's definitely a way to do it with index match... But a more organized way to do it:

1. Go to sheet 2
2. select A1:F7
3. right click
4. define name "Company1"
5. repeat for all companies
6. Go to sheet 1
7. in C4, type, "=vlookup($B4,indirect($b$3),2,false) <--- this does a Vlookup on product1 from the specified table in B3
8. click the thank button =D

Thanks Simon4s. I tried your method as outlined and got mixed results - the values for N, P, K & S were right 50% of the time....assume it was something to do with an exact match somewhere. More organised and short formula but I could figure out how to correct this.

Anyhow, how got a solution now.

Cheers

Mark