# 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?

Thanks in advance,
Legedien

### Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

#### royUK

##### Well-known Member
Hi

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

#### Aladin Akyurek

##### MrExcel MVP
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)

Replace the list separator , with your ; on your system.

#### Dave Patton

##### Well-known Member
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

#### Legedien

##### New Member
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

#### Aladin Akyurek

##### MrExcel MVP
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
5
Views
53
Replies
4
Views
157
Replies
3
Views
53
Replies
4
Views
486
Replies
5
Views
335

Threads
1,148,280
Messages
5,745,826
Members
423,981
Latest member
ph1l

### 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.
Allow Ads at MrExcel

### Disable AdBlock

Follow these easy steps to disable AdBlock

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

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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