# Return Text if Number Matches

#### tlc53

##### Active Member
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.

### Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

#### CSmith

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

##### Well-known Member
What am I missing? Isn't it a VLOOKUP?

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

#### CSmith

##### Well-known Member
The possibility of xxx/xxx...

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

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

#### kweaver

##### Well-known Member
Book1
AB
1Acct CodeDesc
2ABCFirst description
3999Second description
4ABC/111Third descripton
5
6
7
8
9
10
11
12
13
14
15ABC/111
16Third descripton
Sheet2
Cell Formulas
RangeFormula
B16B16=VLOOKUP(B15,A1:B14,2,FALSE)

#### tlc53

##### Active Member
What am I missing? Isn't it a VLOOKUP?

Code:
=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

##### Well-known Member
Doesn't the data possibly look like this?

Book1
AB
1Acct CodeDesc
2999First description
3123/456Second description
4555/666Third descripton
Sheet2

#### tlc53

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

##### Well-known Member
Try

Replacing 000 to 000&"" in your formula.

Meaning just add & and double quotes after 000.

#### tlc53

##### Active Member
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"))

1,089,428
Messages
5,408,164
Members
403,188
Latest member
Sanjana Ramesh