VLookup or If statment

[techgirl]

I have an excel book that if a particular group name is found, then place a number in the next cell. There are multiple groups names, multiple levels within the group, then the appropraite number within that level.


Example...
Column A could have group name A, B, C or D. If group A, then what level (From 1 - 8), then the appropriate number.

Example: If Group A, level 1, then enter the number 50. If group B, level 2, enter 45.

What is the best way to achieve the results??
Thanks, Techgirl

 

[Aladin Akyurek]

For confirming a formula with control+shift+enter: You need to press down the control and shift keys at the same time while you hit the enter key.

 

[techgirl]

May I ask what this does compared to a normal enter key on a formula??

 

[techgirl]

wow, it actually worked with the Ctrl+Shift+Enter. What is the difference please?? ALso, what is the reference to the 1 and zero??

 

[techgirl]

I'm sorry to say, but I moved the database to another sheet and I have re-tried the formula again just like the one that worked (with the Ctrl+Shift+Enter), and now I'm getting the #NA error again. Can you not reference to a different sheet??

 

[Aladin Akyurek]

wow, it actually worked with the Ctrl+Shift+Enter.

Right. Now we can turn to your questions...

What is the difference please?? ALso, what is the reference to the 1 and zero??

Control+shift+enter signals Excel that the formula in question needs to process array objects, not the usual range objects...

In

MATCH(1,IF(...,IF(...,1)),0)

the IF bits lead to TRUE/FALSE evaluations and creates a 1 (the second 1 in the formula) for each TRUE in an array like this:

=MATCH(1,{FALSE;FALSE;1;FALSE;...},0)

where the look up value of 1 (the first 1) is compared to values in FALSE;FALSE;1;FALSE;...} using exact match (indicated by the 0 bit).
The above would yield: 3.

Look for "arrays" in Excel's Help [or ? ] for more.

 

[Aladin Akyurek]

I'm sorry to say, but I moved the database to another sheet and I have re-tried the formula again just like the one that worked (with the Ctrl+Shift+Enter), and now I'm getting the #NA error again. Can you not reference to a different sheet??

Yes. You just put the right sheet names. You must still do CSE.

 

[techgirl]

Thank you, I overlooked the sheet name; even though I selected the correct sheet, when I clicked back into the main sheet, it changed the reference.

Thank you for all of your information of knowledge.

 

[Aladin Akyurek]

Thank you, I overlooked the sheet name; even though I selected the correct sheet, when I clicked back into the main sheet, it changed the reference.

Thank you for all of your information of knowledge.

You are welcome. Thanks for providing feedback.