How JustAnswer Works:

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

Ask Richard Your Own Question

Richard
Richard, Software Specialist
Category: Microsoft Office
Satisfied Customers: 16842
Experience:  Over 15 year experience resolving Microsoft Office Issues
32989067
Type Your Microsoft Office Question Here...
Richard is online now
A new question is answered every 9 seconds

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

Resolved Question:

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?

Submitted: 1 year ago.
Category: Microsoft Office
Expert:  Richard replied 1 year ago.
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
Customer: replied 1 year 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.

Expert:  Richard replied 1 year ago.
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
Customer: replied 1 year 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


 

Expert:  Richard replied 1 year ago.
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
Customer: replied 1 year ago.


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

Expert:  Richard replied 1 year ago.
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
Customer: replied 1 year 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

Expert:  Richard replied 1 year ago.
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).


Expert:  Richard replied 1 year ago.
You also need to press CTRL+Shift+Enter when saving the forumla, not just Enter, as it is an array formula

Customer: replied 1 year 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)

Expert:  Richard replied 1 year ago.
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
Customer: replied 1 year ago.

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

Expert:  Richard replied 1 year ago.
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?

Customer: replied 1 year 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.

Expert:  Richard replied 1 year ago.
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, Software Specialist
Category: Microsoft Office
Satisfied Customers: 16842
Experience: Over 15 year experience resolving Microsoft Office Issues
Richard and 4 other Microsoft Office Specialists are ready to help you
Customer: replied 1 year ago.

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


 

Expert:  Richard replied 1 year ago.
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
Customer: replied 1 year 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.

Expert:  Richard replied 1 year ago.
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.

Customer: replied 1 year 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

Expert:  Richard replied 1 year ago.
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.

Customer: replied 1 year 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.

Expert:  Richard replied 1 year ago.
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.

Customer: replied 1 year 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

Expert:  Richard replied 1 year ago.
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.

Customer: replied 1 year 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.

Expert:  Richard replied 1 year ago.
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.

Customer: replied 1 year ago.


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

Expert:  Richard replied 1 year ago.
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
Customer: replied 1 year ago.


Thank you richard. you were fantastic!!!

Expert:  Richard replied 1 year ago.
Your most welcome Bill! you were just as great!

Remember me if you need anything else.

And all the best.

Take care

Richard

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 Customer New York
< Last | Next >
  • 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 Customer New 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!!!! Alex Los 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. GP Hesperia, CA
  • I couldn't be more satisfied! This is the site I will always come to when I need a second opinion. Justin Kernersville, 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. Esther Woodstock, 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. Robin Elkton, Maryland
 
 
 

Meet The Experts:

 
 
 
  • jstinehelfer

    Information Systems Manager

    Satisfied Customers:

    36
    A+ Comptia Certified computer repair
< Last | Next >
  • http://ww2.justanswer.com/uploads/JS/jstinehelfer/2012-6-6_215146_Pictureofme1.64x64.png jstinehelfer's Avatar

    jstinehelfer

    Information Systems Manager

    Satisfied Customers:

    36
    A+ Comptia Certified computer repair
  • http://ww2.justanswer.com/uploads/JA/JasonJames122/2011-12-16_135647_jasonjamesheadshotweb.64x64.jpg JasonJames122's Avatar

    JasonJames122

    Computer Enthusiast

    Satisfied Customers:

    0
    I manage all information systems (network of Windows PC's and servers with Microsoft Office products) for a small business
  • http://ww2.justanswer.com/uploads/JE/jessmagz/2012-6-6_18129_jm.64x64.jpg Jess M.'s Avatar

    Jess M.

    Computer Support Specialist

    Satisfied Customers:

    301
    Computer Software Specialist for more than 10 years
  • http://ww2.justanswer.com/uploads/CH/chutz747/2011-11-6_14231_me.64x64.jpg The-PC-Guy's Avatar

    The-PC-Guy

    Computer Manager

    Satisfied Customers:

    274
    20 years experience providing remote computer support
  • http://ww2.justanswer.com/uploads/SP/spherrod/2012-6-6_174244_1000852.64x64.JPG Steve Herrod's Avatar

    Steve Herrod

    Computer Support Specialist

    Satisfied Customers:

    125
    Trained in Microsoft Office from 2000 through to latest version 2010.
  • http://ww2.justanswer.com/uploads/SE/Seashore2011/2011-12-6_33418_111205223015.64x64.jpg Chris L.'s Avatar

    Chris L.

    Support Specialist

    Satisfied Customers:

    124
    Certified Expert with over 10 years experience.
  • http://ww2.justanswer.com/uploads/KR/krkljatorm/2012-6-24_153417_ITMiro.64x64.jpg IT Miro's Avatar

    IT Miro

    Computer Scientist

    Satisfied Customers:

    97
    Bachelor's Degree in Information Technology, Microsoft Certified Professional