Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Find value in a range

0

Hi, Is anyone can please help me out with the below?. Please refer to my attachment spreadsheet for sample data.

Apart from using vlookup I am thinking there must be a easier way of how to work this out simpler and quicker.

How to 1: For each record, check if N2, O2, P2, Q2, R2, S2  is within range C2 to L2. If they are, return the value in U2 v2, W2, X2, Y2 and Z2

How to 2: Find a perfect couple, triple and quardruple that most occurred in all records. In this case, perfect couple are 9 and 19 which occurred 3 times, and perfect triple are 9, 19 and 20 which occurred 2 times, and perfect quardruple are 9, 10, 19, 20 and 2, 4, 10, 20.

Thank you all kindly

Hatuau

Answer
Discuss

Answers

0

Please try this formula in cell U2 of your worksheet and copy it from there to the range U2:Z6.

=IFERROR(IF(MATCH(N2,$C2:$L2,0),N2),"")

I'm not sure I correctly understand what you want to do. The formula will look for the value in N2 in the range C2:L2. If successful it copies the value of N2 to its own location. Therefore it returns the results you indicate in row 2 but not in the other rows since range N3:S6 is blank.

Note that each thread can only have one question. In this thread we can deal with your first question. After that has been answered to your satisfaction the next question should be in another thread.

Discuss

Discussion

Hi Variatus, 
Thank you for your great help in my question 1. Yes you are right, I would like to check these number for all records, looks like I have to copy all checking numbers and paste them to all other records.

Thank you kindly
Hatuau
Hatuau Nov 6, '18 at 10:58 pm
Hello Hatuau, If the checking numbers are not generated automatically by some other system in your workbook my guess is that your concept - the basic design of your workbook - isn't perfect yet. If you could share more about what you want to achieve I might be able to contribute an idea. Meanwhile, if you consider the question answered please mark the answer as Selected. Thank you.
Variatus (rep: 4889) Nov 7, '18 at 12:40 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login