Lookup and add values from 3 columns

P

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
 
A

Andy B

Hi

One way is to use something like this:
=OFFSET(D1,MATCH(Sheet2!A1,D: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:D,0)-1,-2)
and
=OFFSET(D1,MATCH(A1,D:D,0)-1,-3)
 
J

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,


Andy B said:
Hi

One way is to use something like this:
=OFFSET(D1,MATCH(Sheet2!A1,D: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:D,0)-1,-2)
and
=OFFSET(D1,MATCH(A1,D:D,0)-1,-3)
Click to expand...
 
A

Andy B

Hi

Yes. Maybe I put it the wrong way round!
This might make more sense:
=OFFSET(Sheet2!D1,MATCH(A1,Sheet2!D: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.


Jim May said:
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,
Click to expand...
 
J

Jim May

Thanks Andy; I got it going.
Jim

Andy B said:
Hi

Yes. Maybe I put it the wrong way round!
This might make more sense:
=OFFSET(Sheet2!D1,MATCH(A1,Sheet2!D: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
Click to expand...
 
Top