Microsoft Office

Microsoft Office Questions? Ask a IT Expert for Support ASAP

Ask a Computer Expert,
Get an Answer ASAP!

Microsoft Office
This answer was rated:

Hi,im looking for a formula that works like a vlookup but

Hi, im looking for a...

Hi, im looking for a formula that works like a vlookup but want it to return different values based upon the occurences in the array. I also do not want it to duplicate values. Any suggestions?

Show More
Show Less
Ask Your Own Microsoft Office Question
Answered in 1 hour by:
8/18/2013
Richard
Richard, Software Specialist
Category: Microsoft Office
Satisfied Customers: 41,168
Experience: Over 15 year experience resolving Microsoft Office Issues
Verified
Thank you for your question, my name is Richard.

Can you tell me please with a bit more detail what you mean by different values based upon the occurrence in the array?

Do you mean you want it to find a criteria within an array of cells?

Thank you
Ask Your Own Microsoft Office Question
Customer reply replied 4 years ago


Hi Richard to give you an example of what i am working on. In one tab of my worksheet, i have a "control library" where multiple different controls are mapped to different risks. One control is documented per row, and are assigned a risk, rating, etc.


 


in my next tab i want to creat a funtion that lists all the controls from the control library that are mappped to a specific risk. So in my example there can be 5 controls that are mapped to 1 specific. When i talk about different values basically i want to have one unique control instance per risk.

Can I look at the spreadsheet please Bill?

- Click the following link: http://www.wikisend.com
- Upload the file to that website
- Once it is uploaded, the resulting page will display a "File ID" number.
- Please, give me that "File ID Number"

Thank you
Ask Your Own Microsoft Office Question
Customer reply replied 4 years ago


Im working from my work computer so unfortunately I cant share the spreadsheet due to securitiy controls. But i am basically building a worksheet from scratch. Hopefully i can explain what i am trying to do here it is


 


The controls library tab has the following columns listed below. each row in the this tab represents 1 unique control so there are no duplicates


 


Risk Level 1


Control Name


Unique Conrol ID


Control Assessment Rating


 


Now in my 2nd tab i want the formula to return all the control names that are mapped to the risk level 1 as i need to list all the controls by Risk Level 1


 

Thanks Bill,

I believe this is what you want.

It will find the occurrences, and will not repeat them if you paste down the formula.

Go to www.wikisend.com

Click Download and download File ID 520800

Let me know if this is what you want please.

Thank you
Ask Your Own Microsoft Office Question
Customer reply replied 4 years ago


Hi Richard i didn't see an excel file where in the zip drive should l look

No zip drive Bill

I think you have an older version of Excel, which shows it as a Zip.

I have saved it as an older version.

Download it from www.wikisend.com with File ID 445380

Let me know if you have any difficulties please

Thank you
Ask Your Own Microsoft Office Question
Customer reply replied 4 years ago

Hi Richard,


 


Its getting there but i have some questions,


 


what does the RoW(1:1)),2 do at the end of the equation. My sheet is set up a little different,y

Row(1:1) is for returning the row number and is part of the function
Small

2 at the end returns column 2 from the array I set which is Sheet1!$A$1:$B$5

It returns the column in Column B as this is the second column (as specified at the end of the function).


Ask Your Own Microsoft Office Question
You also need to press CTRL+Shift+Enter when saving the forumla, not just Enter, as it is an array formula

Ask Your Own Microsoft Office Question
Customer reply replied 4 years ago

Richard its not working?


 


=INDEX(Controls Library!$G$2:$R$452,SMALL(IF(Controls Library!$G$2:$G$452='RCSA Unit 1 Risk Assessment'!A8),ROW('Controls Library'!G2:G452)),ROW(1:1)),4)

I need to see the sheet Bill.

Can you not take out the important data? and make a sample?

Else I cannot know why it is not working.

Make a sample, I only care about the structure of it and some small data.

Then - Click the following link: http://www.wikisend.com
- Upload the file to that website
- Once it is uploaded, the resulting page will display a "File ID" number.
- Please, give me that "File ID Number"

Thank you
Ask Your Own Microsoft Office Question
Customer reply replied 4 years ago

its not the issue with data as there is none, the upload from my cpu has been disabled.

What was the problem you got with the formula?

Did you press SHIFT+CTRL+ENTER when saving it? And not just enter?

What error do you get when you try to upload the file?

Ask Your Own Microsoft Office Question
Customer reply replied 4 years ago


It said i couldn't copy the formula over. I tried to enable editing to allow me to save and edit, but it said i incurred an error. So i had to input by hand.


 


do the {} need to be outside of the first equal sign. Nothing happens when i do that. Also when i try to enter the formula it looks as though it highlights the Yes reference on your example which i overrided with a cell reference which is RCSA UNit 1 Risk Assessment'!A8.

You dont enter in the {}

you press CTRL+SHIFT+Enter when you finish editing the formula, to save it.

But what happens when you try to upload the file to www.wikisend.com?

Richard
Richard, Software Specialist
Category: Microsoft Office
Satisfied Customers: 41,168
Experience: Over 15 year experience resolving Microsoft Office Issues
Verified
Richard and 87 other Microsoft Office Specialists are ready to help you
Ask your own question now
Customer reply replied 4 years ago

when i try to edit the file it says it does not exist. the uploading perspective has been disabled from my CPU


 

Can I log on and look please on the connected computer?

It is easier.

Go to Click Me

Click START MEETING

It will download a program to your computer

Run the program by double clicking it, and it will give you a set of 9 numbers

Give me the numbers at the top of the screen (eg: 456-789-123) in your message to me here please

Thank you
Ask Your Own Microsoft Office Question
Customer reply replied 4 years ago

Sorry richard. I work for a bank, and don't have the ability to download programs. Security issues. i just need to fix this excel issue. not sure if we can take this any further unless you can walk me through it via text.

It makes it hard Bill if I cannot see how your sheet is structured.

Look at my Formula.

=INDEX(Sheet1!$A$1:$B$5,SMALL(IF(Sheet1!$A$2:$A$5="yes",ROW(Sheet1!$A$2:$A$5)),ROW(1:1)),2)

I will break it down

=INDEX(Sheet1!$A$1:$B$5,

This sets the Data Array, so this needs to be covering the Criteria Column and the Value it returns.

SMALL(

This will make sure there are no duplicates when you are copying the formula down.

IF(Sheet1!$A$2:$A$5="yes"

This will say all Cells in Column A (the criteria cell) = yes

ROW(Sheet1!$A$2:$A$5)

This returns the row number when the criteria is matched.

),ROW(1:1)),2)

This returns the value in Column 2 (so in this case B)

When you write the formula, make sure you press SHIFT+CTRL+ENTER to save it, not ENTER.

If you use my Formula correctly, and match it to your rows, it will work Bill, just make sure it is matching correctly with your data please.

Ask Your Own Microsoft Office Question
Customer reply replied 4 years ago


I think the probel is with the IF portion of the formula. Rather than setting it to yes, i am setting it to a cell reference that is the criteria for the search

That is no problem Bill.

It can be text or it can be a Cell for use with the criteria

Sp for example

=INDEX(Sheet1!$A$1:$B$5,SMALL(IF(Sheet1!$A$2:$A$5=E2,ROW(Sheet1!$A$2:$A$5)),ROW(1:1)),2)

Where E2 holds the value that is being used for the criteria.

Ask Your Own Microsoft Office Question
Customer reply replied 4 years ago


Its getting there, but the only issue with the formula is that in a working example of this is that don't know which row the criteria match is going to occur in.

Then you need to set it as text Bill, and not a cell reference.

So replace Yes with the criteria

=INDEX(Sheet1!$A$1:$B$5,SMALL(IF(Sheet1!$A$2:$A$5="yes",ROW(Sheet1!$A$2:$A$5)),ROW(1:1)),2)

As you would need to search the rows for the criteria, so you would already know it beforehand, so it would be done as text in the formula.

Ask Your Own Microsoft Office Question
Customer reply replied 4 years ago


I have done that, I think the issue is with the ,ROW(1:1)) because you are telling it which row to look for the value on

No, as this part tells it which Column and Rows to look for the value on

Sheet1!$A$2:$A$5="yes"

It is looking at Column A, Rows 2-5

Row(1:1) is to make sure it returns unique values.

You need to leave that as it is.

Ask Your Own Microsoft Office Question
Customer reply replied 4 years ago


I think that this is actually working now. the only think i noticed was that its not picking up the first control. where there would be a hit.

Make sure =INDEX(Sheet1!$A$1:$B$5, starts at the very top (in my example $A$1

Even if thats not in the search criteria, it needs to start at A1 (or the top of the column of what you are specifying) as it counts the rows, so if it finds a match on Row 3, that needs to be 3 from the top.

Ask Your Own Microsoft Office Question
Customer reply replied 4 years ago


Its working. By George i think its working. Can i copy this formula to other cells??

yes you can.

Just copy and paste the formula like you do any.

Good work Bill!

I am going offline now, as I am 1 1/2 hour over my sign off time (I really wanted to get this solved for you)!

It was tricky, not being able to look at the file, but we got there.

I will be on for 5 more minutes, just in case you have any problems, so please let me know if you do, else if you can please rate my service, that is very appreciated.

Thank you
Ask Your Own Microsoft Office Question
Customer reply replied 4 years ago


Thank you richard. you were fantastic!!!

Your most welcome Bill! you were just as great!

Remember me if you need anything else.

And all the best.

Take care

Richard
Ask Your Own Microsoft Office Question
Ask Richard Your Own Question
Richard
Richard
Richard, Software Specialist
Category: Microsoft Office
Satisfied Customers: 41,168
41,168 Satisfied Customers
Experience: Over 15 year experience resolving Microsoft Office Issues

Richard is online now

A new question is answered every 9 seconds

How JustAnswer works:

  • Ask an ExpertExperts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional AnswerVia email, text message, or notification as you wait on our site. Ask follow up questions if you need to.
  • 100% Satisfaction GuaranteeRate the answer you receive.

JustAnswer in the News:

Ask-a-doc Web sites: If you've got a quick question, you can try to get an answer from sites that say they have various specialists on hand to give quick answers... Justanswer.com.
JustAnswer.com...has seen a spike since October in legal questions from readers about layoffs, unemployment and severance.
Web sites like justanswer.com/legal
...leave nothing to chance.
Traffic on JustAnswer rose 14 percent...and had nearly 400,000 page views in 30 days...inquiries related to stress, high blood pressure, drinking and heart pain jumped 33 percent.
Tory Johnson, GMA Workplace Contributor, discusses work-from-home jobs, such as JustAnswer in which verified Experts answer people’s questions.
I will tell you that...the things you have to go through to be an Expert are quite rigorous.

What Customers are Saying:

My Expert answered my question promptly and he resolved the issue totally. This is a great service. I am so glad I found it I will definitely use the service again if needed.

One Happy CustomerNew York

Wonderful service, prompt, efficient, and accurate. Couldn't have asked for more. I cannot thank you enough for your help.

Mary C.Freshfield, Liverpool, UK

This expert is wonderful. They truly know what they are talking about, and they actually care about you. They really helped put my nerves at ease. Thank you so much!!!!

AlexLos Angeles, CA

Thank you for all your help. It is nice to know that this service is here for people like myself, who need answers fast and are not sure who to consult.

GPHesperia, CA

I couldn't be more satisfied! This is the site I will always come to when I need a second opinion.

JustinKernersville, NC

Just let me say that this encounter has been entirely professional and most helpful. I liked that I could ask additional questions and get answered in a very short turn around.

EstherWoodstock, NY

Thank you so much for taking your time and knowledge to support my concerns. Not only did you answer my questions, you even took it a step further with replying with more pertinent information I needed to know.

RobinElkton, Maryland

< Previous | Next >

Meet the Experts:

jstinehelfer

jstinehelfer

Information Systems Manager

36 satisfied customers

A+ Comptia Certified computer repair

JasonJames122

JasonJames122

Computer Enthusiast

0 satisfied customers

I manage all information systems (network of Windows PC's and servers with Microsoft Office products) for a small business

Jess M.

Jess M.

Computer Support Specialist

701 satisfied customers

Computer Software Specialist for more than 10 years

The-PC-Guy

The-PC-Guy

Computer Manager

588 satisfied customers

20 years experience providing remote computer support

James K.

James K.

Consultant

260 satisfied customers

Technical Director of IT Company

IT Miro

IT Miro

Computer Scientist

157 satisfied customers

Bachelor's Degree in Information Technology, Microsoft Certified Professional

Chris L.

Chris L.

Support Specialist

147 satisfied customers

Certified Expert with over 10 years experience.

< Previous | Next >

Related Microsoft Office Questions
I have upgraded to Windows 10 and now I can not open some
I have upgraded to Windows 10 and now I can not open some files in Word. It says it can not be found on server, but shows up in file search … read more
Richard
Richard
Software Specialist
41,168 satisfied customers
I mistakenly ordered a second copy of Microsoft Office 365
I mistakenly ordered a second copy of Microsoft Office 365 on 1/15/18 after ordering an initial purchase on 9/2/17. … read more
Josh
Josh
It Support Specialist
Associate Degree
5,140 satisfied customers
How do I set if I want two columns of names? I really don't
I really don't know. I have been assigned to do a church bulletin and there are two columns of names. I'm sorry to be so stupid. … read more
Jason Jones
Jason Jones
AAS Information Technology
6,320 satisfied customers
One of my two controlers connects ok, but the other does
One of my two controlers connects ok, but the other does not. … read more
bbao
bbao
Freelance IT Consultant
74 satisfied customers
I need to learn how to correct spell check on my computer. I
I need to learn how to correct spell check on my computer. I use Office 365 Personel … read more
Mr.Med
Mr.Med
946 satisfied customers
I HAVE TRIED OVER TEN TIMES TO ACCESS MNY MICROSOFT ACCOUNT
I HAVE TRIED OVER TEN TIMES TO ACCESS MNY MICROSOFT ACCOUNT SO i CAN BUY OFFICE WORD. i AM SO FRISTRATED BECAUSE EACH TIME i TRY IT RESULTS IN ME RETRYOING OVER AND OVER CSAN MY OLD ACCOUNT BE DISCONT… read more
bbao
bbao
Freelance IT Consultant
74 satisfied customers
Publisher stopped letting me save files as jpg. there is no
Publisher stopped letting me save files as jpg. there is no data in the jpg file. It will save as a pdf and gif but not jpg or png. Just started happening. … read more
bbao
bbao
Freelance IT Consultant
74 satisfied customers
I paid tech soup 40 dollars for a new replacement for word
I paid tech soup 40 dollars for a new replacement for word and it was to be sent online. a ms partner fo a long time under***@******.*** zzzzzzzzzzzzzzi think zI saw iton my[aptop and tried to downloa… read more
Viet - Computer Tech
Viet - Computer Tech
Computer Technican
Bachelor\u0027s Degree
953 satisfied customers
I am unable to get into my account, with TD. It is through
Hi I am unable to get into my account, with TD. It is through water house. It seems the computer is stuck on easy web. Every time I put on the computer TD pops up. Yet I can not get into my account. But I can do it at someone else's computer. It keeps saying I am all ready registered for easy web banking. There also telling me since it appears that you are currently registered, you should be able to log in and view. Please click easy web to access your account. I tried many times and I cannot get into my account. I would appreciate the help Thank You … read more
bbao
bbao
Freelance IT Consultant
74 satisfied customers
I'm a time user of Word -- Now using Word 2007. However,
I'm a long time user of Word -- Now using Word 2007. However, there is something I cannot determine how to do. That is, I need to remove the signature "block" which appears when a signature line is pl… read more
bbao
bbao
Freelance IT Consultant
74 satisfied customers
How can I open and then close a document without exiting
How can I open and then close a document without exiting word so that I can quickly open the next document? … read more
Viet - Computer Tech
Viet - Computer Tech
Computer Technican
Bachelor\u0027s Degree
953 satisfied customers
I just downloaded Microsoft office last week and the program
I just downloaded Microsoft office last week and the program says there is a problem and is recommending I load the program again. … read more
Cody
Cody
Computer Science
1,723 satisfied customers
I have Microsoft Office installed on my laptop. However,
I have Microsoft Office installed on my laptop. However, when I try to download a Word format attachment from an email I recieve … read more
Kamil Anwar
Kamil Anwar
IT Consultant / Systems Engineer
Bachelor\u0027s Degree
435 satisfied customers
I have just had to reload /restore my Windows software as my
Hi, I have just had to reload /restore my Windows software as my system no longer logged onto the internet. One day to the next, it just would not show me Wifi connection details. I fixed that with th… read more
technicalcat
technicalcat
Computer Science
838 satisfied customers
I'm trying to get an IF/THEN/ELSE statement to work in a
Hi there. I'm trying to get an IF/THEN/ELSE statement to work in a mail merge. Here it is in a nutshell: … read more
bbao
bbao
Freelance IT Consultant
74 satisfied customers
I purchase a new Dell Laptop; order number 324841229;
I purchase a new Dell Laptop; order number###-##-#### Customer number###-##-#### as part off the purchase on 1/12/2018, was the software for Microsoft Office 365 personal. DE000000215429516 … read more
JJesus Perez Gonzale
JJesus Perez Gonzale
Bachelors of Science, Computer Engineering
37 satisfied customers
I just acquired 365 and now I cannot access any of my
I just acquired 365 and now I cannot access any of my documents. whatsup? … read more
Jason Jones
Jason Jones
AAS Information Technology
6,320 satisfied customers
Word update downloaded automatically. I printed a few church
Word update downloaded automatically. I printed a few church bulletins before the update. I print front page and back page in landscape mode so I can fold in the middle. After the update, both front a… read more
bbao
bbao
Freelance IT Consultant
74 satisfied customers
Disclaimer: Information in questions, answers, and other posts on this site ("Posts") comes from individual users, not JustAnswer; JustAnswer is not responsible for Posts. Posts are for general information, are not intended to substitute for informed professional advice (medical, legal, veterinary, financial, etc.), or to establish a professional-client relationship. The site and services are provided "as is" with no warranty or representations by JustAnswer regarding the qualifications of Experts. To see what credentials have been verified by a third-party service, please click on the "Verified" symbol in some Experts' profiles. JustAnswer is not intended or designed for EMERGENCY questions which should be directed immediately by telephone or in-person to qualified professionals.

Disclaimer: Information in questions, answers, and other posts on this site ("Posts") comes from individual users, not JustAnswer; JustAnswer is not responsible for Posts. Posts are for general information, are not intended to substitute for informed professional advice (medical, legal, veterinary, financial, etc.), or to establish a professional-client relationship. The site and services are provided "as is" with no warranty or representations by JustAnswer regarding the qualifications of Experts. To see what credentials have been verified by a third-party service, please click on the "Verified" symbol in some Experts' profiles. JustAnswer is not intended or designed for EMERGENCY questions which should be directed immediately by telephone or in-person to qualified professionals.

Show MoreShow Less

Ask Your Question

x