Lookup function (maybe)

[Guest]

On tab A
number amount
123 10000
123 20000
456 30000
789 15000
789 10000

On tab B
number amount phase
123 10000 1
123 20000 2
123 45000 3
456 30000 1
789 15000 1
789 650000 2
789 10000 3

How can I attach the phase number to the correct element on tab A (ie that
the amount and the number match)? Tab A has 13000 lines, Tab B has 44000
lines

I thought about concatenating the number and amount fields together and
doing a lookup based on that, does anyone have a better solution?

 

[Domenic]

Assuming that Sheet1 contains the information from Tab A, and Sheet2
contains the information from Tab B, try the following...

C2, copied down:

=INDEX(Sheet2!$C$2:$C$8,MATCH(1,(Sheet2!$A$2:$A$8=Sheet1!A2)*(Sheet2!$B$2
:$B$8=Sheet1!B2),0))

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Adjust the
references accordingly.

However, your solution which involves concatenation is more efficient
and you'll probably notice the difference in speed.

Hope this helps!