Look up a cell value based on a cell in another sheet

[spill-the-beans]

Hello all,

I'm looking for a formula that I can put in BA2 in a sheet called Data, and copy the formula down the column.

If Data AE2 is blank, Data BA2 should be blank.

If there is a number in AE2, the formula should look for the matching number in another sheet called Index, in Index column G. The matching number can be in any row, but will only appear once in the whole column.

For the row number that has the pair, look in Index column AG and copy the value in that cell to Data BA2.

Data

[TABLE="width: 500"]
<tbody>[TR]
[TD]1[/TD]
[TD]AE[/TD]
[TD]BA[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1[/TD]
[TD]put 560 here[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]4[/TD]
[TD]put 770 here[/TD]
[/TR]
</tbody>[/TABLE]

Index

[TABLE="width: 500"]
<tbody>[TR]
[TD]1[/TD]
[TD]G[/TD]
[TD]AG[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1[/TD]
[TD]560[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]4[/TD]
[TD]770[/TD]
[/TR]
</tbody>[/TABLE]

Any help on this would be really great.

 

[Asala42]

If I read the problem correctly, it sounds like a straightforward Vlookup.

=IF(AE2="","",VLOOKUP(AE2,Index!$G$1:$AG$100,27,0))

 

[spill-the-beans]

"Straightforward" you say! Thank you very much for a perfect solution.