Return Text if Number Matches

[tlc53]

Hi,

I have been trying to figure out this formula for sometime and feel it's now time to call in the experts.

A2:A10 Account Code (format ### or ###/###)
B2:B10 Account Description (text)

B15 - This is a manual entry tab, you enter the account number
B16 = I want this cell to return the Account Description from B2:B10 depending on the account number entered above, in B15.

Thanks very much.

 

[CSmith]

My suggestion would be to use a UDF supply cell and range optional lookup column and optional return column and get either empty string or description per your request or do you need it to be a formula?

 

[kweaver]

What am I missing? Isn't it a VLOOKUP?

=VLOOKUP(B15,A1:B10,2,FALSE)

 

[CSmith]

The possibility of xxx/xxx...

What am I missing? Isn't it a VLOOKUP?

=VLOOKUP(B15,A1:B10,2,FALSE)

 

[kweaver]

Book1
	A	B
1	Acct Code	Desc
2	ABC	First description
3	999	Second description
4	ABC/111	Third descripton
5
6
7
8
9
10
11
12
13
14
15		ABC/111
16		Third descripton
Sheet2

Cell Formulas
Range	Formula
B16	B16	=VLOOKUP(B15,A1:B14,2,FALSE)

 

[tlc53]

What am I missing? Isn't it a VLOOKUP?

=VLOOKUP(B15,A1:B10,2,FALSE)

Thank you. This works if the account numbers are plainly typed into A2:A10. However, for example, even though the cell A5 visually shows 196 the formula in that cell is actually;

=IF('XPA Data Table'!B839="",TEXT('XPA Data Table'!A839,"000"),TEXT('XPA Data Table'!A839,"000")&"/"&TEXT('XPA Data Table'!B839,"000"))

This seems to be affecting the result and returns #N/A instead of returning the description from B2:B10. Is there a way I can amend the formula to allow for this? Thanks.

 

[kweaver]

Doesn't the data possibly look like this?

Book1
	A	B
1	Acct Code	Desc
2	999	First description
3	123/456	Second description
4	555/666	Third descripton
Sheet2

 

[tlc53]

Doesn't the data possibly look like this?

Yes, that's basically how it looks. I'm noticing that when I enter an account code in the format ###/### the formula works fine and the description shows. However, if the account number is, for example, 999, it returns #N/A

Interesting - if I enter '999 the formula works but it doesn't work with just 999

 

[snjpverma]

Try

Replacing 000 to 000&"" in your formula.

Meaning just add & and double quotes after 000.

 

[tlc53]

Try

Replacing 000 to 000&"" in your formula.

Meaning just add & and double quotes after 000.

Thanks. I see where you were getting at with that but it didn't fix the problem..

=IF('XPA Data Table'!B839="",TEXT('XPA Data Table'!A839,"000")&"",TEXT('XPA Data Table'!A839,"000")&"/"&TEXT('XPA Data Table'!B839,"000"))