Using Validation Ref # within LOOKUP

M

MichaelRobert

How can I use the Validation Ref # to describe the cell contents to be
'looked up'

My LOOKUP formula is:

=IF(B7="Gray",LOOKUP(B17,Accessories!A22:C24,Accessories!B22:B24),LOOKUP(B17,Accessories!A22:C24,Accessories!C22:C24)) [CTRL-SHIFT-ENTER]

B17 contains a text string provided by Validation that does not match the
nomenclature in the LOOKUP Table. (Validation returns REF# = '1-4" tube' or
REF# = '2-4" tubes'. But the LOOKUP lists 4")

I can deal with the quantities (1 or 2) elsewhere, but I need help in
figuring out how to modify the 'B17' component

Thanks.

Mike
 
L

Luke M

Depending on what all validation choices you have, this might work:

=IF(B7="Gray",LOOKUP(VALUE(MID(B17,3,1)),Accessories!A22:C24,Accessories!B22:B24),LOOKUP(VALUE(MID(B17,3,1)),Accessories!A22:C24,Accessories!C22:C24))

Takes out the 3rd character from B17, converts it to a number, and then
looks it up in your lookup table. If your lookup contains the following:
4"

Modifed formula should be:
=IF(B7="Gray",LOOKUP(MID(B17,3,2),Accessories!A22:C24,Accessories!B22:B24),LOOKUP(MID(B17,3,2),Accessories!A22:C24,Accessories!C22:C24))
 
M

MichaelRobert

I have just realized I made a dumb posting. I withdraw my question and
apologize for wasting your time.

I misread the Error Message 'Validation!#REF#', and thought it was part of
the code ...

Mike
 
M

MichaelRobert

Luke:

Many thanks for your idea. I think it'll work. Maybe my post was not so dumb
after all - just triggered by a misread :)

Mike

Luke M said:
Depending on what all validation choices you have, this might work:

=IF(B7="Gray",LOOKUP(VALUE(MID(B17,3,1)),Accessories!A22:C24,Accessories!B22:B24),LOOKUP(VALUE(MID(B17,3,1)),Accessories!A22:C24,Accessories!C22:C24))

Takes out the 3rd character from B17, converts it to a number, and then
looks it up in your lookup table. If your lookup contains the following:
4"

Modifed formula should be:
=IF(B7="Gray",LOOKUP(MID(B17,3,2),Accessories!A22:C24,Accessories!B22:B24),LOOKUP(MID(B17,3,2),Accessories!A22:C24,Accessories!C22:C24))
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


MichaelRobert said:
How can I use the Validation Ref # to describe the cell contents to be
'looked up'

My LOOKUP formula is:

=IF(B7="Gray",LOOKUP(B17,Accessories!A22:C24,Accessories!B22:B24),LOOKUP(B17,Accessories!A22:C24,Accessories!C22:C24)) [CTRL-SHIFT-ENTER]

B17 contains a text string provided by Validation that does not match the
nomenclature in the LOOKUP Table. (Validation returns REF# = '1-4" tube' or
REF# = '2-4" tubes'. But the LOOKUP lists 4")

I can deal with the quantities (1 or 2) elsewhere, but I need help in
figuring out how to modify the 'B17' component

Thanks.

Mike
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top