Excel - IF with VLOOKUP problem

[Livia8]

Another one of those that are probably staring at me in the face:

This is my formula which isn't working properly:

=IF(E3="Office 1",VLOOKUP((TEXT(C3,"@")),($A$1:$B$168),2,1),IF(E3="Office 2",VLOOKUP((TEXT(C3,"@")),($A$89:$B$167),2,1),IF(E3="Office 3",VLOOKUP((TEXT(C3,"@")),($A$169:$B$205),2,1),"")))

In column A I have a series of codes representing let's say the access codes for the door numbers listed in B. The columns have a total of 205 rows.

In Cell E3 I want to be able to type either Office 1, Office 2 or Office 3, and in Cell C3 I want to be able to type the codes as in Column A so that the relevant door number may be returned in B3.

What am I doing wrong?

Thanks

Livia

 

[GlennUK]

So, your Office 1 range covers rows 1 to 168, and,
your Office 2 range covers rows 89 to 167, and,
you Office 3 rnage covers rows 169 to 205.

How can the overlap between Office 1 and Office 2 exist?

Have you typed =TEXT(C3,"@") in a cell to see what the result is? What do you expect the result to be?

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[Livia8]

Sorry,

That was a typo:

The range for Office 1 goes from 1 to 87,
the range for Office 2 goes from 88 to 167 and
the range for Office 3 goes from 168 to 205.

The "text" part of the formula in B3 should "reads" the format of C3 as text as opposed to number format (it needs to remain a number in A).

The result I'm tying to obtain is that when I type the Office # and the code I'm then able to find the door number
(the doors are just an example).

Thanks

 

[GlennUK]

And have you tried ...

"Have you typed =TEXT(C3,"@") in a cell to see what the result is? What do you expect the result to be?"

as I asked?

It sounds as though you haven't. I know what TEXT does, having used it many thousands of times, and I'm sure it's not doing what you expect.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[koresnordic]

As Glen said:

Have you typed =TEXT(C3,"@") in a cell to see what the result is?



Graham

 

[UniqueFD]

You need to break these lengthy formulae down into components and check each component:
=IF(E3="Office 1","YES","NO) [blue]type "Office 1" in E3 and ensure you get "YES"[/blue]
=IF(A1=TEXT(A1,"@"),"YES","NO") [blue]to check you are getting a positive match (and therefore the VLOOKUP will find it)[/blue]
=VLOOKUP(A1,$A$1:$B$168,2,1) [blue]to check you are returning the correct column[/blue]
=VLOOKUP([green][similar value to A1 not elsewhere in the column][/green],$A$1:$B$168,2,0) [blue]? do you want an exact match or an aproximate match (check how VLOOKUP works)[/blue]
When you're sure each component is giving the correct result, then string them together for the first office test and, once this is working correctly, bring in the other offices.

What you are doing specifically wrong depends on what is being returned and what the data is. It could be your use of VLOOKUP is incorrect; comparing mis-matching data types (text/numbers); I would also check your brackets around your IF statements [hint]!!!!

Tony
___________________________________________________
Reckless words pierce like a sword,
but the tongue of the wise brings healing (Solomon)

 

[GlennUK]

Hi UniqueFD,

your second check "=IF(A1=TEXT(A1,"@"),"YES","NO")" might be confusing to Livia, as I'd wait and see what she finds out about using TEXT before advising her to use it.

Also, your hint about the brackets around the IF statements is very misleading ... there are no mistakes there for what Livia originally aimed for.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[UniqueFD]

Glen,

Thanks for pointing these out. You're right, my [hint] was incorrect. Sorry, Livia - a slip of the brain.

My point was to break the whole formula into manageable, predictable chunks. I had prepared my suggestions a while before I actually posted (interruptions) and didn't see the third, fourth and fifth posts in this thread at all. Next time I will 'refresh' before posting.

Tony
___________________________________________________
Reckless words pierce like a sword,
but the tongue of the wise brings healing (Solomon)

 

[Livia8]

Glenn and Graham,

I HAVE tried to type =TEXT(C3,"@") in a cell, "THANK YOU", and the result is that it's turning C3 (0000000150) into 150, which I believe is what I was trying to get at. What was your point?

Could I have some help as to how to resolve my problem instead of "constructive" criticism? If I'd known the answer I wouldn't be here wasting everyone's time...

Tony, many thanks for your suggestion; the result I'm currently getting in B with my formula is a figure that is out of the range I was using: for example, I type code 123 in Office 1 and I should get the Door Number in Office 1 (i.e. 413). However, I'm getting Room Number 308 which is in Office 2 (and 123 is NOT the code for that door at all). Also, I need the exact match, not just something similar. Hope this makes sense.

Thanks.

 

[Corgano]

Try changing
=IF(E3="Office 1",VLOOKUP((TEXT(C3,"@")),($A$1:$B$168),2,1),IF(E3="Office 2",VLOOKUP((TEXT(C3,"@")),($A$89:$B$167),2,1),IF(E3="Office 3",VLOOKUP((TEXT(C3,"@")),($A$169:$B$205),2,1),"")))


To:
=IF(E3="Office 1",VLOOKUP((TEXT(C3,"@")),($A$1:$B$168),2,FALSE),IF(E3="Office 2",VLOOKUP((TEXT(C3,"@")),($A$89:$B$167),2,FALSE),IF(E3="Office 3",VLOOKUP((TEXT(C3,"@")),($A$169:$B$205),2,FALSE),"")))

I didn't think you could use a numeric as the last element of a vlookup but it makes sense... it's True or False and typically the 1 would mean true.

So you know, if the last element is true the vlookup will look for the closest match and not the exact match. Setting this to false forces it to only return the exact match.

 

[Corgano]

You can also try...

=CHOOSE(IF(A2<=87,1,IF(A2<=167,2,3)),"Office 1","Office 2","Office 3")

 

[UniqueFD]

Livia
you said:
==>...I type code 123 in Office 1 and I should get the Door Number in Office 1
but what code do you have in column A? Is it:

Text string "0000000123"
Text string "123" or
The number 123?

Glenn & Graham's point is to encourage you to check that the TEXT function is returning your data in the same format as the column you are checking.


Tony
___________________________________________________
Reckless words pierce like a sword,
but the tongue of the wise brings healing (Solomon)

 

[Livia8]

Corgano,

I'll check your suggestions and let you know, many thanks for your answer.

Tony,

I'll review my spreadsheet and get back to you, thanks also.

Glenn and Graham,

Apologies if I've been short with you, that's what I turn into when I'm having a rotten day, three hours sleep and about 16 kegs of coffee - God help my colleagues...

I'll take 10 deeps breaths and then I'll compare yet again the results of the "TEXT" to the format of the original column.

I'll get back to you all on Monday, meanwhile have a good weekend and thanks.

 

[UniqueFD]

Livia,

sorry you've had a bad day. Hope the weekend brings lots of rest, relaxation and positive thoughts!

Regards,

Tony
___________________________________________________
Reckless words pierce like a sword,
but the tongue of the wise brings healing (Solomon)

 

[jdhilljr]

Here's a suggestion.

=CHOOSE(RIGHT(A1,1),VLOOKUP(B1,Sheet2!1:65536,2,0),VLOOKUP(B1,Sheet3!1:65536,2,0))

and so on up to 29 choices.

Good luck,

Jim

 

[GlennUK]

Hi Livia,

hope you're in a better mood today ( jeez, you try and help, and get it the neck sometimes ). Anyway, here's a solution that might work ...
=OFFSET(B1,MATCH(VALUE(C3),INDIRECT(CHOOSE(RIGHT(E3,1),"A1:A88","A89:A168","A169:A205")),0)-1+CHOOSE(RIGHT(E3,1),0,88,168),0,1,1)

By the way, your use of TEXT was hard to fathom, as you hadn't told us what the value of C3 was, nor what you wanted it turned into. TEXT(c3,"0") makes more sense, as does TEXT(c3,"#"). In fact, I'm using VALUE in my formula, as I'm thinking that maybe the numbers in column A aren't text.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.