vlookup
 

Hi I have a table of data contaning
department name op number production


AA1 AA2 AA3
press op 10 40
wash op 10 90

A5 = press
b5 = op 10

i want to return the value of the identified op in b5 from the
identified department in a5
i am trying to use vlookup to achieve this


any suggestion on the best way forward


thanks


kevin

vlookup
 

If you have the department name and want to return the op number, then maybe:

=vlookup(a5,aa1:ac3,2,false)

Debra Dalgleish has lots of notes:
http://www.contextures.com/xlFunctions02.html (for =vlookup())
and
http://contextures.com/xlFunctions02.html#Trouble

kevcar40 wrote:

Hi I have a table of data contaning
department name op number production

AA1 AA2 AA3
press op 10 40
wash op 10 90

A5 = press
b5 = op 10

i want to return the value of the identified op in b5 from the
identified department in a5
i am trying to use vlookup to achieve this

any suggestion on the best way forward

thanks

kevin

--

Dave Peterson

vlookup
 

thanks for reply dave

i want to check both department and op then return the value


On 1 Oct, 13:48, Dave Peterson wrote:
If you have the department name and want to return the op number, then maybe:

=vlookup(a5,aa1:ac3,2,false)

Debra Dalgleish has lots of notes:http://www.contextures.com/xlFunctions02.html*(for =vlookup())
andhttp://contextures.com/xlFunctions02.html#Trouble





kevcar40 wrote:

Hi I have a table of data contaning
department name * * * * * *op number * * * *production

AA1 * * * * * * * * * *AA2 * * * * * * * * * *AA3
press * * * * * * * * * op 10 * * * * * * * * *40
wash * * * * * * * * * op 10 * * * * * * * * * 90

A5 = press
b5 = op 10

i want to return the value of the identified op in b5 from the
identified department in a5
i am trying to use vlookup to achieve this

any suggestion on the best way forward

thanks

kevin

--

Dave Peterson- Hide quoted text -

- Show quoted text -

vlookup
 

=index($ac$1:$ac$3,match(1,(a5=$aa$1:$aa$3)*(b5=$a b$1:$ab$3),0))

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can only use the whole column in xl2007.



kevcar40 wrote:

thanks for reply dave

i want to check both department and op then return the value

On 1 Oct, 13:48, Dave Peterson wrote:
If you have the department name and want to return the op number, then maybe:

=vlookup(a5,aa1:ac3,2,false)

Debra Dalgleish has lots of notes:http://www.contextures.com/xlFunctions02.html (for =vlookup())
andhttp://contextures.com/xlFunctions02.html#Trouble





kevcar40 wrote:

Hi I have a table of data contaning
department name op number production

AA1 AA2 AA3
press op 10 40
wash op 10 90

A5 = press
b5 = op 10

i want to return the value of the identified op in b5 from the
identified department in a5
i am trying to use vlookup to achieve this

any suggestion on the best way forward

thanks

kevin

--

Dave Peterson- Hide quoted text -

- Show quoted text -

--

Dave Peterson

vlookup
 

Dave
THankyou very much the array works a treat
thanks again

kevin

Dave Peterson wrote:

=index($ac$1:$ac$3,match(1,(a5=$aa$1:$aa$3)*(b5=$a b$1:$ab$3),0))

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can only use the whole column in xl2007.



kevcar40 wrote:

thanks for reply dave

i want to check both department and op then return the value

On 1 Oct, 13:48, Dave Peterson wrote:
If you have the department name and want to return the op number, then maybe:

=vlookup(a5,aa1:ac3,2,false)

Debra Dalgleish has lots of notes:http://www.contextures.com/xlFunctions02.html (for =vlookup())
andhttp://contextures.com/xlFunctions02.html#Trouble





kevcar40 wrote:

Hi I have a table of data contaning
department name op number production

AA1 AA2 AA3
press op 10 40
wash op 10 90

A5 = press
b5 = op 10

i want to return the value of the identified op in b5 from the
identified department in a5
i am trying to use vlookup to achieve this

any suggestion on the best way forward

thanks

kevin

--

Dave Peterson- Hide quoted text -

- Show quoted text -

--

Dave Peterson