You are describing the VLOOKUP() function

=VLOOKUP(B6,'Sheet 3'!$J$1:$0$500,6,0)



"Mr. Fine" wrote:

I want to compare the value in one cell to another column of cells in another
work sheet and once the matching value is found I wan to return a different
cells value that is on the same line as the matching cell. Example Sheet 1
value is 34 in B6, so I want it to search Sheet 3 colum J for the value 34
which is found on row 351. When a match is found the value returned needs to
be from colum O so this would be O351. Does anybody know how to setup this
function?