Need a formula for multiple vlookup value with match function & vertically check interval.

[jd16]

Hi guys,

i need a formula for attached worksheet.

Thanks & Regards

[JohnTopley]

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution.

Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

To change a Title go to your first post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

And explain how you get your results.

[jd16]

Thanks for advice, now corrected

[Glenn Kennedy]

Please explain HOW you reached your expected results....

[jd16]

Thanks for reply.
I have done it manually, and i need a formula for it for doing it automatically.
Logic is, when we give some input in match value cells(in this case i have given 4 values 1,2,3,4 and it can be any other values like 4,6,8,9 or whatever).

C3:C6 values should check for match with all values within row E3:K3, then if match founded it should always take first matching number( in this case values 1,2,3,4 found a match 1&2 between e3:k3 but as you can see it has taken position of 1 between e3:k3 and given as result 4 which is the position of 1(h3) in row e3:k3.

if you need more clarification on this please feel free.

Thanks & Regards

[Glenn Kennedy]

Yes.... I need more help here. I saw that as a possible explanation for the first row.... but how do you get the other results. The same logic does not seem to work.

[JohnTopley]

Let me have a go ..

4 is position of the first 1.

3 is the INTERVAL after the 2 on the first line (has to be ignored as we are finding the sequence 1, 2 3,4) until the 2 (F4) on the second line. We now ignore the 3 & 4 so 11 is the INTERVAL until we reach the 3 (in F6).

We ignore the 2 & 1 in this line and the INTERVAL (in my view) is 5 [not 6] until we reach the 4 in G7.

It appears we then start the sequence again, finding 1, so the INTERVAL is 1 (not 2)

Clear as mud!!!!????

[jd16]

Yes you were right, Thanks for correcting me..
I have attached new worksheet, please have a look on it.

1. given match values should start calculation with 1,2,3..,.., from e3, when match find any of given 4 vales, formula should give result in m3, now again calculation should start from just after from previous match were founded,and counting should be in running continuation after an row will end, for example in this case countdown is again started just after 1st match which is 1, so started from 2 but again matched with given values so result came out in m4 and again countdown started from j3,

2. countdown will not stop if row will end, for example countdown started from i4 but not ended with k4 and not even with k5 till a match founded in e6, so as result 11 came in m7.

3. interval will be counted just after previous match to just before new match.

new sheet will clear some more air, just have a look on it.

Thanks & Regards

[jd16]

Thanks for a prompt reply it was fine with previous sheet, please have a look on new sheet.


Thanks & Regards

[JohnTopley]

Having looked at your latest file I am utterly confused so I'll drop out of this thread.

[bebo021999]

You are confusing people, aren't you?
I inteprete like this:

Untitled.png

Is it your logic?

[jd16]

absolutely correct...
attaching some more snapshots for reference on your way

[Glenn Kennedy]

I followed the explanation... eventually... but wasted a lot of time playing with SUMPRODUCT. This array formula, in O3, copied down, does the job.

=IFERROR(SMALL(IFERROR(1/(1/((($E$3:$K$7=1)+($E$3:$K$7=2)+($E$3:$K$7=3)+($E$3:$K$7=4))*((COLUMN($E$3:$K$7)-COLUMN($E$3)+1)+7*(ROW($E$3:$K$7)-ROW($E$3))))),""),ROWS($1:1))-SUM($O$2:O2),"")

Array Formulae are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...

[jd16]

Hi Glenn,
Thanks for your lots of efforts
but unfortunately formula in not working based on c3 to c6, when we change the value in cell c3 to c6, the result still remain same
result should be always change if user change the value of any cell or all the cells between c3 to c6.

Thanks & Regards

[JohnTopley]

Try this change to Glenn's formula:

=IFERROR(SMALL(IFERROR(1/(1/((($E$3:$K$7=$C$3)+($E$3:$K$7=$C$4)+($E$3:$K$7=$C$5)+($E$3:$K$7=$C$6))*((COLUMN($E$3:$K$7)-COLUMN($E$3)+1)+7*(ROW($E$3:$K$7)-ROW($E$3))))),""),ROWS($1:1))-SUM($O$2:O2),"")


..confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

Glenn's formula had the values "hard-coded" so it now references values in C3:C6.

[Glenn Kennedy]

Yep. My mistake. I forgot to change them from hard-coded values to cell references. I'd also suggest one additional change to the formula, just in case a) the grid isn't always 7 columns wide and b) you don't spot the significance of the 7 in the formula:

=IFERROR(SMALL(IFERROR(1/(1/((($E$3:$K$7=$C$3)+($E$3:$K$7=$C$4)+($E$3:$K$7=$C$5)+($E$3:$K$7=$C$6))*((COLUMN($E$3:$K$7)-COLUMN($E$3)+1)+COLUMNS($E:$K)*(ROW($E$3:$K$7)-ROW($E$3))))),""),ROWS($1:1))-SUM($O$2:O2),"")

Anyhow...

You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

[jd16]

Yeah!! working absolutely fine John
Many Thanks to you both Glenn & John