• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

vLOOKUP and IF or what?

S

Siga

Member
Hello ninjas,

I need your advice again. Please find the uploaded file.
Sheet "COGS", rows 67:91. I need to catch values in column I (highlighted in yellow) from sheet "Maps" based on concatenate cells Sales Channel + Geography (e.g. Trade Australia" or CCG Japan). Where vLOOKUP is just perfect. BUT... My values should depend on division. If Division is X, I need to catch values from column L in sheet "Maps", if Division is Y - from column M in sheet "Maps".
Is there a solution that I put "Division X" in cell I67 in sheet "COGS" and excel understands that it has to look values in column L in sheet "Maps" and vice versa. I put "Division Y" in cell I67 and it has to look values in column M in sheet "Maps".
If sulution requires additional columns or rows I am fine too.

Siga
 

Attachments

  • Siga_V3.xlsx
    393.3 KB · Views: 23
bosco_yip said:
Another option,

In I70, formula copied down :

=VLOOKUP(A70,Maps!$G:$M,6+(I$67="Division Y"),0)

Regards
Bosco
Click to expand...

Hi Bosco,

What if I have more than 2 divisions? Let's say 3 or 4, that means column N = Division Z and Column O = Division W in sheet Maps.

Trying to think one step further..I tested Chihiro formula and it works with 3 Divisions as well.

Siga
 
Siga said:
Hi Bosco,

What if I have more than 2 divisions? Let's say 3 or 4, that means column N = Division Z and Column O = Division W in sheet Maps.

Trying to think one step further..I tested Chihiro formula and it works with 3 Divisions as well.

Siga
Click to expand...
=INDEX(Maps!$G$1:$M$231,MATCH(COGS!$A70,Maps!$G$1:$G$231,0),MATCH(COGS!$I$67,Maps!$G$1:$M$1,0))
 
Siga - - - -
Screen Shot 2017-04-11 at 19.50.29.png
why not like above ( ... maybe You use ',' instead of ';' )?
Cell [I68] one formula and
'just' change original 6 to I$68 in Your VLOOKUP-formula,
after that 'no matter' how many Divisions
and
if You make 'DropDown' to cell I67 then no need to write 'Division' something...
 
vletm said:
Siga - - - -
View attachment 40649
why not like above ( ... maybe You use ',' instead of ';' )?
Cell [I68] one formula and
'just' change original 6 to I$68 in Your VLOOKUP-formula,
after that 'no matter' how many Divisions
and
if You make 'DropDown' to cell I67 then no need to write 'Division' something...
Click to expand...

Hi vletm,

why not like to exchange brains? :)
could you do it in my excel and upload? Not sure I catch your idea correctly, but really impressed with drop down suggestion!
Appreciate your help a lot!

Siga
 
Siga, Siga, Siga
... because You only like l o o o n g f o r m u l a s :( mine don't!
those formulas needs to protect for mistake clicks.
( just one wrong click and ... it could or couldn't notice for weeks... months)
 

Attachments

  • Siga_V3.xlsx
    389.1 KB · Views: 4
vletm said:
Siga, Siga, Siga
... because You only like l o o o n g f o r m u l a s :( mine don't!
those formulas needs to protect for mistake clicks.
( just one wrong click and ... it could or couldn't notice for weeks... months)
Click to expand...

vletm,

you are completely wrong, because Siga likes hammock in Grand Cayman Islands :) :) :) and not long formulas! But life is cruel so Siga is in the North Pole hanging with polar bears.

what do you mean by not liking yours? I do. but as it is a different sheet and I really do not know how to write macros, I went with Bosco's formula (at least I know how to deal with it). I would feel bad asking you to write macros every time I need. I am spending time on them, so who knows - maybe one day I will beat you (after brain exchange thing :) )

One wrong click...and I will blame Bosco, vletm, and the rest of the world. hahaha! Goinmg to study your excel example. Thank you very much!

Siga
 
Siga said:
Hi Bosco,

What if I have more than 2 divisions? Let's say 3 or 4, that means column N = Division Z and Column O = Division W in sheet Maps.

Trying to think one step further..I tested Chihiro formula and it works with 3 Divisions as well.

Siga
Click to expand...
If you like long formula and without the 2 helper columns.

Try,

In I70, array formula copy down :

=VLOOKUP(D70,IF(Maps!H$1:H$500=B70,Maps!I$1:M$500),MATCH(I$67,Maps!I$1:M$1,0),0)

p.s.
1] Array formula to be confirmed pressing with SHIFT+CTRL+ENTER 3 keystrokes together.

2] A validation dropdown list has placed on I67 for your selection.

Regards
Bosco
 

Attachments

  • Siga_V3.xlsx
    374.2 KB · Views: 7
Siga
Here, more more snow in this morning and of course minus degrees!
I've seen sheets with full of formulas.
One day someone click in one cell and of course pressed delete. ... and she didn't notice that!
Few months later, other user wondered - why why why?
Why nothing has changed for few months
... until checking few hundreds of cells formulas and
... finally she found one empty cell.
Basic macros are not so difficult ...
and writing itself has same procedure as with formulas.
... but now You'll get that dropdown too :)
 
You must log in or register to reply here.
Back
Top