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

  • Thread starter Thread starter pmg2003
  • Start date Start date
P

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
 
...
...
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
Click to expand...

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)
 
Use match and index instead and add one to it,

=INDEX(Sheet2!B1:B500,MATCH(Sheet1!A1,Sheet2!A1:A500,0)+1)
 
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))
 
Back
Top