lookup function help (1 column over & 1 row down)

[pmg2003]

I am trying to create a function that searches thru a column on a seperate
sheet and finds the value in the next column over and the next row down.
when i enter a number on column A on sheet 1 it look in column A on sheet2
and once it finds a match it skips over one column and then one row down to
get that value to fill in column B on sheet 1

If

Sheet1:A1 = 90125

go to sheet 2 and find that value in Column A

Sheet2:A10 = 90125

Go one column over and on row down

Sheet2:B11 = ABWH

then use that value in sheet 1,

Sheet1:B1 = ABWH


tia,

Paul

 

[Harlan Grove]

...
...

If

Sheet1:A1 = 90125

go to sheet 2 and find that value in Column A

Sheet2:A10 = 90125

Go one column over and on row down

Sheet2:B11 = ABWH

then use that value in sheet 1,

Sheet1:B1 = ABWH

In Sheet1!B1 (you gotta lose the 123 3D reference syntax - exclamation points,
not colons, separate worksheet name and cell address in Excel),

=OFFSET(Sheet2!$A$1,MATCH(A1,Sheet2!$A$1:$A$999,0),1)

 

[Peo Sjoblom]

Use match and index instead and add one to it,

=INDEX(Sheet2!B1:B500,MATCH(Sheet1!A1,Sheet2!A1:A500,0)+1)

 

[J.E. McGimpsey]

One way:

If the value in sheet1 is guaranteed to be in sheet2:

=INDEX(Sheet2!B:B,MATCH(A1,Sheet2!A:A,0)+1)

If it's not guaranteed:

=IF(ISNA(MATCH(A1,Sheet2!A:A,0)), "",
INDEX(Sheet2!B:B,MATCH(A1,Sheet2!A:A,0)+1))