# Find value in a range

0

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

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.

### 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: 2938) Nov 7, '18 at 12:40 am