look for a value in a range and copy value in next cell

[mcgill55]

Hello,

I have an excel document with 2 worksheets. One is used for reference (page 1) and the other to do calculation (page 2).

I'd like to have a formula that would be in page 2 in cell b1 that would look at the value of cell a1 and go look into a range of value on page 1 (cells a1 to a20) to find the matching value, and than copy the value of the right next to it in cell b1 of page 2.

i.e. - if I have the value 8 in cell b1 of page 2, the formula will look for 8 in the range a1 to a20 of page 1 (for the sake of this example the value 8 would be in A8 of page 1) than it would copy the value of B8 (the cell right next to A8) of page 1 into the cell b1 of page 2.

I hope I'm not to confusing... and that you can help me. I woudl rather use a formula but if it is impossible, could you help me with the VBA code that would do that.

Thank you in advance...

 

[Jonmo1]

Try an index/match...

=INDEX('Page 1'!B1:B20,MATCH(A1,'Page 1'!A1:A20,0))

Hope that helps..

 

[mcgill55]

Perfect ! it works exactly like I needed !!

thank you very much