Lookup and add values from 3 columns

[Pritch14]

I have a worksheet with stock locations in, Column A = Aisle, Column B
Location Column C= Level Column D=Item number

I would like to return the value in column A through C on a new shee
when I put in a certain item number, that matches value in column D
kind of like a pivot table but not!

Any ideas

 

[Andy B]

Hi

One way is to use something like this:
=OFFSET(D1,MATCH(Sheet2!A1,D,0)-1,-1)
This will return whatever is in column C - where the number in Sheet2!A1
matches column D. To return the same from B and A use:
=OFFSET(D1,MATCH(A1,D,0)-1,-2)
and
=OFFSET(D1,MATCH(A1,D,0)-1,-3)

 

[Jim May]

Andy:
Are you assuming OPs info in sheet2
and your using offset formulas in sheet1?
Also, if in sheet1 are you using same column
structure with D = item number?
Thanks,

so far I'm just getting #N/A

Thanks,

Hi

One way is to use something like this:
=OFFSET(D1,MATCH(Sheet2!A1,D,0)-1,-1)
This will return whatever is in column C - where the number in Sheet2!A1
matches column D. To return the same from B and A use:
=OFFSET(D1,MATCH(A1,D,0)-1,-2)
and
=OFFSET(D1,MATCH(A1,D,0)-1,-3)

 

[Andy B]

Hi

Yes. Maybe I put it the wrong way round!
This might make more sense:
=OFFSET(Sheet2!D1,MATCH(A1,Sheet2!D,0)-1,-1)
Where the formula uses A1 from the sheet you are in and the formula looks up
the info from sheet 2

--
Andy.

Andy:
Are you assuming OPs info in sheet2
and your using offset formulas in sheet1?
Also, if in sheet1 are you using same column
structure with D = item number?
Thanks,

so far I'm just getting #N/A

Thanks,

 

[Jim May]

Thanks Andy; I got it going.
Jim

Hi

Yes. Maybe I put it the wrong way round!
This might make more sense:
=OFFSET(Sheet2!D1,MATCH(A1,Sheet2!D,0)-1,-1)
Where the formula uses A1 from the sheet you are in and the formula looks up
the info from sheet 2

--
Andy.


Column

 

[Andy B]

Thanks for the thanks!

--
Andy.

Thanks Andy; I got it going.
Jim

looks