In sheet 2:

=SUMPRODUCT(--(Name&Code=A2),Amount)


"Gerard" wrote:

Is it possible to lookup a valid match of 2 values in separate columns, and
provide the value of a related third column?

Sheet1

Name Code Amount
Ant 003 100
Bil 002 200
Claire 001 300

Sheet2

Full code Amount
Ant003

The Full code in Sheet2 is entered manually and I wish to keep this.

The Amount cell in Sheet2 should lookup the full code (eg Ant003) directly
from the concatenation of the Name and Code fields in Sheet1 (in this case it
should display 100)

Is it possible to retrieve the amount automatically without having to create
a separate lookup table, as this is the current method and I want to lose
this intermediary step?