a better way than multiple if conditions

  • Thread starter Thread starter Dave W
  • Start date Start date
D

Dave W

I am using a formula than I would like to expand

=IF(B9="M.01_sheetgoods",VLOOKUP
($C9,sheetgoods_pricing,3,0),IF(B9="M.04_hardware",VLOOKUP
(C9,hardware_pricing,3,0),""))

Its all on one line and will be getting bigger as I add
more if conditions. I remember someone posting that there
can't be more than 7 in one formula. Is there a better way

Also these references like M.01_sheetgoods are also used
in data validation lists. First I pick a category, then I
pick the item in that category.It then returns
information about that item in other cells.

I had a master materials_list but it has grow to large to
scroll through. So I have broken it up into categories to
make looking up data alittle faster.

any ideas would be a great help

Thanks
 
Dave - try something like this:

=VLOOKUP(C9,B9&"_pricing",3,0)

If your range names are all "something"_pricing, this
should work. If you need the M.01 with it, you can still
do it with string extraction formulas. I'm not at my
office, so I can't test this fully. If it doesn't work, or
you need more help and don't hear any other responses, post
back and I will answer again tomorrow.
 
One way which might work,
based on the structure described in your post ..

Try:

=IF(ISBLANK(B9),"",VLOOKUP(C9,INDIRECT(TRIM(MID(B9,SEARCH("_",B9)+1,99))&"_p
ricing"),3,0))
 
Thank you Max

That worked great...very clever!
-----Original Message-----
One way which might work,
based on the structure described in your post ..

Try:

=IF(ISBLANK(B9),"",VLOOKUP(C9,INDIRECT(TRIM(MID(B9,SEARCH ("_",B9)+1,99))&"_p
ricing"),3,0))

--
Rgds
Max
xl 97
---
Please respond in thread
xdemechanik <at>yahoo<dot>com
----



.
Click to expand...
 
Thank you for the concept Dion

the next post from Max filled in the blanks for me
The solution works great!
 
Back
Top