Return Text if Number Matches

tlc53

Active Member
Joined
Jul 26, 2018
Messages
365
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.
 

Some videos you may like

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
Joined
Jan 13, 2020
Messages
678
Office Version
365, 2010, 2007
Platform
Windows, Mobile, Web
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
Joined
May 8, 2018
Messages
921
Office Version
365, 2010
What am I missing? Isn't it a VLOOKUP?

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

kweaver

Well-known Member
Joined
May 8, 2018
Messages
921
Office Version
365, 2010
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
Joined
Jul 26, 2018
Messages
365
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
Joined
May 8, 2018
Messages
921
Office Version
365, 2010
Doesn't the data possibly look like this?

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

tlc53

Active Member
Joined
Jul 26, 2018
Messages
365
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
Joined
Oct 2, 2008
Messages
1,259
Office Version
2016
Platform
Windows
Try

Replacing 000 to 000&"" in your formula.

Meaning just add & and double quotes after 000.
 

tlc53

Active Member
Joined
Jul 26, 2018
Messages
365
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"))
 

Forum statistics

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

This Week's Hot Topics

  • help please
    SORRY NOT ANY GOOD AT EXCEL SO HELP WOULD BE MUCH APPRECIATED this formula is in a sheet called ignore...
  • two formulas needed
    Hello, I'll try my best to explain this: First formula needed in Sheet1 cell A2: If Sheet1 cell B2 = Sheet2 cell B2 then return a 1. If not then...
  • Dynamic Counts
    Good afternoon, we are tidying up some data & the data seems to be growing quicker than we are tidying it up! What we confirm (by reviewing it...
  • Help Excel formula eliminate duplicate values and keep only 2 identical rows.
    as picture below column A has a duplicate value. but the values are not the same as the rule. sometimes 4 rows, sometimes 10 rows or 7 or 9...
  • Macro Compile Error Sub or Function not defined
    Hello, I am trying to run macros from a validation list, all macros have been created and run perfectly on there own but I'm getting a compile...
  • Last row combined with Current Region VBA
    I'm generally happy finding the last row of data through something like Lastrow = Cells(Rows.Count, "D").End(xlUp) but I don't always receive data...
Top