Horizontal vs. vertical lookups with formulas

[Peggy2015]

Hello,

How do I write a formula to look something up values on the same worksheet and then look up another section on the same worksheet and use that information below it? Please see below table.

How do I lookup information from company A that will give me the amount for Stationery in one cell and then look up information from Company B's stationery and the amount in another cell?

Company A	Description	Amount
1234	Stationery	100.00
2345	Calendars	50.00
3456	Paper	25.00
Company B
1234	Stationery	150.00
5678	Pencils	100.00

<tbody>
</tbody>

 

[Marcelo Branco]

Maybe

	A​	B​	C​	D​	E​	F​	G​
1​	Company A​	Description​	Amount​		Company​	Description​	Result​
2​	1234​	Stationery​	100,00​		Company A​	Stationery​	100,00​
3​	2345​	Calendars​	50,00​		Company B​	Stationery​	150,00​
4​	3456​	Paper​	25,00​
5​
6​	Company B​
7​	1234​	Stationery​	150,00​
8​	5678​	Pencils​	100,00​
9​

Criteria in columns E:F

Formula in G2 copied down
=INDEX(INDEX(C:C,MATCH(E2,A:A,0)):$C$1000,MATCH(F2,INDEX(B:B,MATCH(E2,A:A,0)):$B$1000,0))

M.

 

[Peggy2015]

Hi Marcelo,
Thank you for your help. This works like a charm!
Best wishes,
Peggy