Lookup valid combination of multiple cells

[Guest]

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?

 

[Guest]

Try in the Amount field (B2) on Sheet2:

=SUMPRODUCT(--(Sheet1!$B$2:$B$4=RIGHT(A2,3)),--(Sheet1!$A$2:$A$4=LEFT(A2,LEN(A2)-3)),--(Sheet1!$C$2:$C$4))

This assumes Code is always 3 characters long.

HTH

 

[Guest]

That's worked! Thank you very much.

Just out of interest, if the code was either:

- Always 2 characters long
- Variable

What impact would this have on the function?

Once again, thanks for your help.

Gerard

 

[Guest]

If 2 characters long:

=SUMPRODUCT(--(Sheet1!$B$2:$B$4=RIGHT(A2,2)),--(Sheet1!$A$2:$A$4=LEFT(A2,LEN(A2)-2)),--(Sheet1!$C$2:$C$4))

If the length is variable, then we would have to test for a numeric string
(the code) and determine its length so it's rather more complicated.

 

[Guest]

In sheet 2:

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

 

[Guest]

That works perfectly, thanks.

In sheet 2:

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