• 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.

Excel baby, IF(AND( or something more elegant?

J

jswstella

New Member
hI Guys,

Apologies for the long message

i'm new to chandoo (about a month),(joined today)and a baby in excel years (about 2 months)ive learnt loads here but am baffled at 99.9r% of it,anyways

I have to enter a number into a blank cell that will return a country or region the number refers to ie-1000-1999=Wales and 2000-2999= England etc etc.


my lowest numbers are in col B, highest in C and my blank cell is D2, i know i can use

=IF(AND(D2>=B2,D2<=C2),"Wales","") to find numbers that refer to Wales but how do i link this to find if the numbers refer to UK,Scotland ,etc.


I could probably use loads of IF(AND's but that would be hideous (i have around 30 Number ranges)and i'd like a more elegant way of doing this and to learn something new.


Thanks in advance and i know its probably really easy so don't laugh (remeber i'm only 2 months old)

Ps i can re-arrange my data as it isnt fixed or maybe use a pivot table or some VBA(REALLY new to these)
 
Jswstella


Firstly welcome to the Chandoo.org Forums


Your problem will better be served by the use of a lookup using VLookup or Index Match

Have a read of

http://chandoo.org/wp/2008/11/19/vlookup-match-and-offset-explained-in-plain-english-spreadcheats/
 
Thanks Hui

I've already read it, thinking it was close to what i wanted, but i'll grab a large cup of coffee and try and digest it again.
 
Can you post your data ?

http://chandoo.org/forums/topic/posting-a-sample-workbook
 
Thanks for the ultra quick repsonse BTW Hui,

Am i right in thinking that VLOOKUP and MATCH require a list?

I don't have a list of 1000 Through 1999, i just know that between 1000 and 1999 refers to wales, so if i enter 1824 excel will display "wales" or 2150 will return "England"etc.


My boss has the full list in paper format and wondered if i could find the location of somewhere without searching through the (rather large)book.

Heres how i put the data in excel (they are all in Col a,b,c, (copy paste issues))


Wales 68199 68899

Northern Ireland 71000 71999

Other UK 67100 67299

69000 70999

England North East 39000 39999

41000 41999

43000 43999

49000 49999

England North West 32000 34999

40100 40999

42000 42999

45000 47999

England Yorkshire and the humber 36000 38999

44100 44499

48000 48999

England West Midlands 20000 20999

23000 24999

29000 30999

England East Midlands 25000 28999

31000 31999

Engalnd Eastern Region 15000 19999

22000 22999

52000 52999

England South West 50000 50999

53000 55999

57000 57999

60000 60999

63000 63999

66000 66999

England South East 51000 51999

56000 56999

58000 59999

61000 62999

64000 65999

England London 10000 14799
 
3 questions


Just using the

England West Midlands 20000 20999

23000 24999

29000 30999

bit

What Column is "England West Midlands" in?

Are 20000 20999 in Columns B & C?

Do the 3 Ranges all refer to England West Midlands ?
 
Thanks again Hui

all regions are in A

lower numbers in B

and higher numbers in C

and yes the numbers under each region refer to that region

I'll try and upload the actual data as you suggested if it will make this clearer.
 
See how this goes

Rearrange your data to be in

A2:C36

[pre]
Code: 
Wales			68199	68899
Northern Ireland	71000	71999
Other UK		67100	67299
Other UK		69000	70999
England North		39000	39999
England North		41000	41999
England North		43000	43999
England North		49000	49999
England N W		32000	34999
England N W		40100	40999
England N W		42000	42999
England N W		45000	47999
England Tork		36000	38999
England Tork		44100	44499
England Tork		48000	48999
England W Mid		20000	20999
England W Mid		23000	24999
England W Mid		29000	30999
England East mid	25000	28999
England East mid	31000	31999
Engalnd East reg	15000	19999
Engalnd East reg	22000	22999
Engalnd East reg	52000	52999
England SW		50000	50999
England SW		53000	55999
England SW		57000	57999
England SW		60000	60999
England SW		63000	63999
England SW		66000	66999
England SE		51000	51999
England SE		56000	56999
England SE		58000	59999
England SE		61000	62999
England SE		64000	65999
England London		10000	14799
[/pre]

and then use

=INDEX(A2:A36,SUMPRODUCT(($E$2>=$B$2:$B$36)*($E$2<=$C$2:$C$36)*ROW()))

where your Number is in E2
 
=INDEX($A$2:$A$36,SUMPRODUCT(($E$2>=$B$2:$B$36)*($E$2<=$C$2:$C$36)*ROW())

will work anywhere

provided the data is in 3 columns A, B & C as above

and your query number is in E2


What errors ?

can you post your data?
 
Hope this link to hot file works

copy pasted the formula in D1

Any number i enter into E2 reports back "Wales"

http://hotfile.com/dl/119561800/be88414/Book1.xlsx.html
 
Whoops

This will do the job

=INDEX($A$2:$A$36,SUMPRODUCT(($E$2>=$B$2:$B$36)*($E$2<=$C$2:$C$36)*ROW(A2:A36))-1)
 
Nailed it!

Well done Hui and thank you very much.

I half understand the formula and now realise that entering a region for each row (so no blanks)would simplify it.

I love it that such a forum exists and people are kind enough to help out others.
 
You must log in or register to reply here.
Back
Top