Complex Uses of Vlookup Function

[Davidprince]

Ladies and Gentlemen
I have a table of 10,000 numbers. I am trying to work out a formula that will look up a table based on the result in the list of numbers. Initially I am looking at the last five results, wanting three occurences but may want to test for 6 or 7 numbers (with three occurences). The example should explain a little clearer:

Numbers
27
29
8
11
29
11

Based on the lookup table for 11, we find that the numbers are 11,3,8,26,29. As you can see from the table, at the first occurence of 11, there are three occurences being 11,8 and 29. That would then start a formula to look for the next occurence of one of those five numbers.

I initially tackled the problem with a vlookup picking the 11 and checking against the lookup table to see if numbers from the lookup table matched, recording that result and then summing the hits into another cell. That worked, but there were a few errors.

So there are two questions, firstly is the lookup formula the best approach and secondly can the list numbers be selected dynamically (ie: 27,29,8,11,29; 29,8,11,29,11 etc) so that each group of five is looked at? I think I am doing the latter of these, but it seemed incorrect.

Thanks
David

 

[SkipVought]

hi,

???

A lookup formula returns ONE value

???

Have no idea what you're working with or where you are going! Your explanation is not at all clear.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

BTW, a cogent example would be helpful, showing ALL your tables, lookup example and expected results.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Davidprince]

Hi Skip
Thanks for the comments....quite right, the explanation wasn't detailed enough. The list below may make it clearer:

Number G H I J
27
29
8
11
29 a b c d
11
27
29
2
1
29
8
36

formula in cell a is =IF(VLOOKUP(A5,$Q$1:$U$37,1,FALSE)=A5,"1",IF(VLOOKUP(A5,$Q$1:$U$37,2,FALSE)=A4,"1",IF(VLOOKUP(A5,$Q$1:$U$37,3,FALSE)=A4,"1",IF(VLOOKUP(A5,$Q$1:$U$37,4,FALSE)=A4,"1",IF(VLOOKUP(A5,$Q$1:$U$37,1,FALSE)=A4,"1",0)))))

$Q$1:$U$37 contains a table (the vlookup table). There are 5 numbers associated with each instance of a number. For example, with 29 the numbers are 29,11,17,30,34. The formula should check to see if there are three instances of those numbers in the five results. So in this case, the idea of the formula is to check at row 5 whether in the last 5 numbers (including the current number) whether 29 and the numbers associated with it have occurred three times in the last five results.

The formula in cell a (noted above compares the current number to each of the five numbers associated with 29 (29,11,17,30,34) and if there is a match puts 1 into that cell when there is a match. This process occurs for the other cells above it, with variations of the formula. This results in a formula in columns K,L,M,N,O which evaluates to 1 or 0 (1 if there is a match, 0 if no match). I then report in column P if the sum of K:O >= 3 as there need to be three qualifiers.

Hope this makes more sense.
Thanks
David

 

[SkipVought]

I am expecting an example that I can COPY n PASTE into mt sheet to test your formula.

Your example does not make sense for all five values as you only reference two A5 & A4!!!

EXAMPLE PLEASE!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

...and perhaps you ought to explain WHAT it is that you are trying to accomplish rather than HOW you think it ought to be performed.

What is the business case for this exercise?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

For instance WHERE is the example that shows that " For example, with 29 the numbers are 29,11,17,30,34"?

You STATE the relationship but do not SHOW that relationship in ANY example!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Gavona]

Avoid doing essentially the same lookup 5 times - very inefficient

If your data is in column A
Then use column B as a helper column containing a Match formula to identify the row in your lookup table.
In column C use 5 countifs and check that the sum is greater than 1 (3 or more matches found)
(Greater than 1 because you KNOW you have a match on the current row so don't need to test that)

Untested (it is late and I have not followed all the nuances of your requirement!)

B6: = Match(A6,LookupTable,0)
C6: = (countif(A2:A5,index(LookupTable,B6,1) +
countif(A2:A5,index(LookupTable,B6,2) +
countif(A2:A5,index(LookupTable,B6,3) +
countif(A2:A5,index(LookupTable,B6,4) +
countif(A2:A5,index(LookupTable,B6,5) ) >1

C6 will return True if 3 or more of the values in A2:A6 are a match to one of the 5 numbers in the row of your lookup table identified by the formula in B6.

Gavin