• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Vlookup value in several columns

Gandalf

Member
Hi All, I wonder if anyone can help. I have a spreadsheet for lottery numbers, ColA is the date, cols B:F are the results. Col I is the numbers 1 to 50. For each number in col I would like to look up when it last appeared in any cols B:F and enter the corresponding date into a cell in col J. I have tried different ways - VLOOKUP (eg in J2 the formula =VLOOKUP(I2,B:F,1,FALSE) and INDEX/MATCH but always get an error message. Many thanks
 
How about
=IFERROR(INDEX($A$2:$A$100,AGGREGATE(14,6,(ROW($A$2:$A$100)-ROW($A$2)+1)/($B$2:$F$100=I2),1)),"not found")
 
please Upload Excel Sheet for what You Want !!!
Hi, ooops - sorry. spreadsheet attached. What I was trying to do is in Column L I would like the date from column A that the number from Columns C:G last appeared ie the most recent date on which the number was drawn. For instance the number 1 should show as 29/01/2021 in Col I. Many thanks
 

Attachments

  • euromillions-past-draws.xlsm
    86 KB · Views: 5
Try =AGGREGATE(14,6,((1/($C$3:$G$1145=K3))*$A$3:$A$1145),1)
You might want to organize your data using Excel Tables if you plan to add data regularly. In that case you will have to rebuild the formula, but the general idea is the same
 

Attachments

  • Copy of euromillions-past-draws.xlsm
    83.9 KB · Views: 9
Thank you all for all your help, I am nearly there!!! In the spreadsheet I have used this formula in Col L
=IFERROR(INDEX(A:A,MATCH(K3,C:C,0)),IFERROR(INDEX(A:A,MATCH(K3,D:D,0)),IFERROR(INDEX(A:A,MATCH(K3,E:E,0)),IFERROR(INDEX(A:A,MATCH(K3,F:F,0)),INDEX(A:A,MATCH(K3,G:G,0))))))

However it will return the first date for, say, number 42 in Col D when there is a later date in, say, Col F, which is the one I want. Is there any way round this, perhaps finding all the dates for a particular number and returning the MAX value? Am I getting into some VBA stuff which is probably beyond me? Or is there a way to do it using HLOOKUP?

Thank you all again for your help
 

Attachments

  • euromillions-past-draws.xlsm
    85.2 KB · Views: 4
As you have the latest numbers at the top
=IFERROR(INDEX($A$2:$A$10000,AGGREGATE(15,6,(ROW($A$2:$A$10000)-ROW($A$2)+1)/($C$2:$G$10000=K3),1)),"not found")
 
Anything wrong with post #5?
Hi pecoflyer

Nothing at all, I just tried it and it worked brilliantly. Sorry if I offended you, but using AGGREGATE which I have never used before was a steep learning curve and I still haven't got it straight in my own mind what it does.
 
Perhaps search the Net for examples of the AGGREGATE function. There should be many tutorials out there
Interesting thing is you do not need to sort your data to get the correct result
 
Back
Top