Need to set formula for all cells in a COLUMN to bring up X name if Y number appears

**Badzel** · 04-25-2014, 12:21 PM

HI Guys

If anyone finds a minute, please help me out with that Function.
On one sheet I have List of Ticket numbers. Each ticket is assigned with unique number. For example
A1 5123
A2 4876
A3 5123

A4 2313
A5 5543

I want to create SHEET2 and make sure that if in A1 (Sheet2) appears number from range A1:A3 SHEET1.

Column B1(SH2) will bring up specific name

If in A15 appears number from range A1:A3(Sheet1), same name will come up in B15

If in A54 appears number from range A4:A5(Sheet1) different name will come up

I really hope it makes sense for you guys. Thanks for looking !

**Richard Buttrey** · 04-25-2014, 12:26 PM

Hi, and welcome to the forum.

Have you tried the VLOOKUP() function?

**playaller** · 04-25-2014, 12:50 PM

Something like below will get you started.

1. Paste your numbers in column A on Sheet1.
2. On Sheet 2 A1 for example we use the number 5123
3. In B1 paste the code below. If the number is found within range A1:A3 on Sheet1 then the word FOUND will show otherwise it will be blank.

Adjust to your liking.

Please Login or Register  to view this content.

**Badzel** · 04-25-2014, 02:17 PM

....................

**Badzel** · 04-25-2014, 02:17 PM

HI Guys thanks for all replies
It may just be me, but I still can't get it to work like it should be
Let me give you a better picture

SHEET1
Column A Contains ticket numbers
1. 124
2. 584
3. 356
4. 846
5. 689
6. 478

Sheet 1 COLUMN B
Contains Names of individuals who are responsible for these tickets

Column A Column B
1. 124 Michael
2. 584 Michael
3. 356 Michael
4. 846 Ellis
5. 689 Ellis
6. 478 Kris

I will be producing report by pulling ticket numbers from third party software and pasting them into Column A in SHEET 2. Week after I will use Sheet 3 etc etc...
I need a function in which I will list specific numbers, or range of numbers, and have correct name filled in that cell if number is found

Playaller function you advised seemed to do the job but I could only search for one number using one function
=IFERROR(IF(VLOOKUP(Sheet1!A21,Sheet1!A2:A33,1,FALSE),"FOUND",""),"Error value")

on A21 Sheet1 I have included number and YES in fact it found it in A2:A33 range, but that's still far away from what I want to achieve.

There is a range of about Fifty unique numbers which are distributed between five people.
About 10 different reference numbers assigned per name

Please help guys

I believe there is a way. Maybe I should link Ticket and Name cells somehow and than try different function ???
Im green when it comes to that stuff

**Richard Buttrey** · 04-25-2014, 02:22 PM

Originally Posted by Badzel

HI Guys thanks for all replies
It may just be me, but I still can't get it to work like it should be
Let me give you a better picture

You could give us an even better picture if you upload the workbook.

**Badzel** · 04-25-2014, 02:37 PM

So this is how part of the list looks like

25-04-2014 19-35-39.png

Multiple Problem Tickets are assigned between few chaps,

On Sheet Two I will Paste the problem tickets with updated stats and want these names to match the number

**playaller** · 04-25-2014, 04:07 PM

Also be advised: In your post you advised the function only looks up one number. Note the formula looks up the number to the left of it. So in sheet two if you have a list of numbers, when you drag this formula down the column it will match the person with each number in the list on sheet1 to the numbers you have aligned in column A on sheet 2 and as updated below, display the name.

Try this. As I understand you want the name of the person displayed within the given range.

- Paste in B2 on Sheet 2.
- Edit $A$2:$B$10 range. (Must contain both columns to capture name). Currently its looking from row 2 to 10. $A = Start range $B = End range.
- Drag drown to the end of your numbers in column A.

Please Login or Register  to view this content.

For a new range, paste the code in the next level of cells, adjust the range to search for and drag down accordingly.

If this doesn't work, as stated by Richard we'll need to see the workbook with an example of what you want.

**Badzel** · 04-26-2014, 07:01 AM

playaller You are TOP lad ! Thank you so much for this

Works perfect, that's what I was looking for !

**playaller** · 04-27-2014, 05:42 PM

You're welcome Badzel. Glad I could assist.

Need to set formula for all cells in a COLUMN to bring up X name if Y number appears

LinkBack

Thread Tools

Rate This Thread

Display

Need to set formula for all cells in a COLUMN to bring up X name if Y number appears

Re: Need to set formula for all cells in a COLUMN to bring up X name if Y number appears

Re: Need to set formula for all cells in a COLUMN to bring up X name if Y number appears

Re: Need to set formula for all cells in a COLUMN to bring up X name if Y number appears

Re: Need to set formula for all cells in a COLUMN to bring up X name if Y number appears

Re: Need to set formula for all cells in a COLUMN to bring up X name if Y number appears

Re: Need to set formula for all cells in a COLUMN to bring up X name if Y number appears

Re: Need to set formula for all cells in a COLUMN to bring up X name if Y number appears

Re: Need to set formula for all cells in a COLUMN to bring up X name if Y number appears

Re: Need to set formula for all cells in a COLUMN to bring up X name if Y number appears

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Formula to Rank the number of times a word appears in a column dynamic with Sorting

How to bring and copy row data of a cell text when the text appears in another column?

formula to count number of times the letter "x" appears in a column

Need a formula that will tally the number of times a month appears in a column

Formula for value in a column that appears in the highest row number

Bookmarks

Bookmarks

Posting Permissions