Vlookup value in several columns

[Gandalf]

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

 

[herofox]

please Upload Excel Sheet for what You Want !!!

 

[Fluff13]

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")

 

[Gandalf]

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

 

[pecoflyer]

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

 

[Gandalf]

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")

Thank you, I'll give it a go

 

[Gandalf]

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

 

[Fluff13]

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")

 

[pecoflyer]

Anything wrong with post #5?

 

[Gandalf]

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.

 

[pecoflyer]

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

 

[Gandalf]

Thank you for your help.