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?
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Company A
[/TD]
[TD]Description
[/TD]
[TD]Amount
[/TD]
[/TR]
[TR]
[TD]1234
[/TD]
[TD]Stationery
[/TD]
[TD]100.00
[/TD]
[/TR]
[TR]
[TD]2345
[/TD]
[TD]Calendars
[/TD]
[TD]50.00
[/TD]
[/TR]
[TR]
[TD]3456
[/TD]
[TD]Paper
[/TD]
[TD]25.00
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Company B
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1234
[/TD]
[TD]Stationery
[/TD]
[TD]150.00
[/TD]
[/TR]
[TR]
[TD]5678
[/TD]
[TD]Pencils
[/TD]
[TD]100.00
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Marcelo Branco]

Maybe


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][td="bgcolor: #DCE6F1"]

D​

[/td][td="bgcolor: #DCE6F1"]

E​

[/td][td="bgcolor: #DCE6F1"]

F​

[/td][td="bgcolor: #DCE6F1"]

G​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

Company A​

[/td][td]

Description​

[/td][td]

Amount​

[/td][td][/td][td]

Company​

[/td][td]

Description​

[/td][td]

Result​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

1234​

[/td][td]

Stationery​

[/td][td]

100,00​

[/td][td][/td][td]

Company A​

[/td][td]

Stationery​

[/td][td]

100,00​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

2345​

[/td][td]

Calendars​

[/td][td]

50,00​

[/td][td][/td][td]

Company B​

[/td][td]

Stationery​

[/td][td]

150,00​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

3456​

[/td][td]

Paper​

[/td][td]

25,00​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

6​

[/td][td]

Company B​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

7​

[/td][td]

1234​

[/td][td]

Stationery​

[/td][td]

150,00​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

8​

[/td][td]

5678​

[/td][td]

Pencils​

[/td][td]

100,00​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

9​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

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