# Can't manage a formula...

#### Legedien

##### New Member
Mornin' all!

Excel really is my weak spot and I need help with creating a formula. If anybody would feel helpful, I'd really appreciate it!

Well, the excel-problem I have:
In cell A1 I've entered number 12134. In cell B1 I've added the formula =LEFT(A1;2) (I must add that my formulas are in swedish, but I'll try to translate it as correct as possible).

So, the number I now see in B1 is 12. Then in cell C1 I have the formula =IF(B1>10;1019). And since the value is BIGGER than 10, in C1 I see the number 1019.

The problem is that I want to add, that if the number in B1 also is SMALLER than 19, the TRUE is also 1019.

And to make it even messier, I'd like to add more conditions/terms. Like, if the number is BIGGER than 19 or SMALLER than 21 the TRUE is 2021.

Anyone?

Legedien

### Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
Hi

You need to look at Nested IF statements in your help and also lots of examples on this site

In B1 enter:

=LEFT(A1,2)+0

In C1 enter:

=IF(AND(B1>10,B1<19),1019,"?")

For more conditions, you should use VLOOKUP, not IF. Something like this...

=VLOOKUP(B1,{0,0;12,1019;19,"?"},2,0)

You might try a lookup or Vlookup with the information either in the formula or in a supporting table.

one example

=LOOKUP(B1*1,{0,10,19,21;0,1019,2021,2050})

Notes
1. the *1 converts the text in B1 to a number
2. check the thresholds
for example 19 will show 2021

3. check the commas and semicolon conversions to your system

Hi all!

Thank you very much for you replies!!

I'd like to ask you about the formulas you've entered. This one: =IF(AND(B1>10,B1<19),1019,"?") I understand without problems and made it work. Very interesting!

But! These formulas...
=VLOOKUP(B1,{0,0;12,1019;19,"?"},2,0)
=LOOKUP(B1*1,{0,10,19,21;0,1019,2021,2050})

...I do not understand. And since we use both commas and semicolons in our system I have no clue where to change those in your formulas.

The first formula I understood, and could therefor change the commas.

Could anyone explain the LOOKUP formulas a bit?

TIA
/Legedien

On 2002-10-18 03:45, Legedien wrote:
Hi all!

Thank you very much for you replies!!

I'd like to ask you about the formulas you've entered. This one: =IF(AND(B1>10,B1<19),1019,"?") I understand without problems and made it work. Very interesting!

But! These formulas...
=VLOOKUP(B1,{0,0;12,1019;19,"?"},2,0)
=LOOKUP(B1*1,{0,10,19,21;0,1019,2021,2050})

...I do not understand. And since we use both commas and semicolons in our system I have no clue where to change those in your formulas.

The first formula I understood, and could therefor change the commas.

Could anyone explain the LOOKUP formulas a bit?

TIA
/Legedien

The VLOOKUP as well as the LOOKUP version use an in-lined table...

It would be much easier for you to set up a 2-column list, give it a name, and use that name in the VLOOKUP formula... like in

=VLOOKUP(B1,Table,2)

See for an example

http://www.mrexcel.com/board/viewtopic.php?topic=25724&forum=2&6

Replies
7
Views
148
Replies
2
Views
346
Replies
0
Views
131
Replies
1
Views
442
Replies
7
Views
314

1,221,425
Messages
6,159,834
Members
451,592
Latest member

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back