What EXCEL code should I use to display a text matching a number in a differeny cell?

Question

0.00/5 (No votes)

See more:

What formulas in EXCEL should I use to display a text into another cell, that matches a number which is adjacent to that cell with the text?

Example:

Foreman, George 2

I want the cell to detect the number 2 but display the name it's next to, in this instance, 'Foreman, George'.

Any combination of code that can make this happen?

Thanks,

What I have tried:

I've tried adding the MATCH formula, but didn't know how to display the text. LOOKUP, TEXT etc. all didn't do what I wanted.

Posted 16-Aug-18 20:44pm

Member 13952214

Updated 21-Aug-18 22:14pm

Add a Solution

Comments

Richard MacCutchan 17-Aug-18 4:04am

Use the FIND and RIGHT, LEFT or MID functions to find specific parts of the text in a cell.

CHill60 17-Aug-18 12:01pm

Is "Foreman, George 2" in a single cell or is that in 2 cells or 3 cells? It's not clear from your quesiton

Member 13952214 21-Aug-18 23:54pm

2 is in a different cell

CHill60 21-Aug-18 4:45am

Hello?
Do you still need help?

Member 13952214 21-Aug-18 23:55pm

Sorry, I was away on vacation

CHill60 22-Aug-18 4:14am

Hope it was a good vacation! I've submitted a solution for you

Member 13952214 25-Aug-18 0:27am

THANKS! I just moved the row around and it matches up just how I wanted.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Accepted Answer · 2018-08-21T22:15:00

Imagine this test scenario...

In Columns A and B I have the values

Column A                 Column B
Foreman, George            2
Bloggs, Joe                3
Bond, James                4

and in cell G1 I have the value 2If I use the VLOOKUP function like this

=VLOOKUP(G1,A:B,2,FALSE)

I will get the #N/A result. The clue is in the tooltip for VLOOKUP (the bold is from me) ...

Quote:
Looks for a values in the leftmost column of a table, and then returns a value in the same row from a column you specify. By default, the tale must be sorted in an ascending order.

There are a couple of ways around your problem...

1. Reverse the order of your columns

Column A                 Column B
  2                      Foreman, George
  3                      Bloggs, Joe
  4                      Bond, James

Then the formula to use is

=VLOOKUP(G1,A:B,2,FALSE)

2. Drag a formula down column C

=A1

Then the forumula to use is

=VLOOKUP(G1,B:C,2,FALSE)

You can always hide column C if you want to keep the original "look" of the spreadsheet