I have a calc spreadsheet for a raffle. There is a row for each ticket purchaser. And along with their details we have 2 columns representing the start and end ticket number for that person.
Given a winning ticket number I would like to find the the winning row. The row where winningTicket >= startTicket AND winningTicket <= endTicket
I’m thinking that a combination of INDEX ROW and MATCH might work but I can’t quite find it.
If the purchase list is sorted on ticket numbers and you have consecutive ranges (no missing tickets), This should work:
=MATCH(<ticket>;<column for purchasers' first tickets>;1)
You may want to check the result for validity (i.e. ticket number <= purchaser's last ticket).
Thanks Keme, and apologies for my slow reply. Reading the documentation again I see that MATCH is similar to VLOOKUP except it operates on a single column. So I could use MATCH too.
thanks.
Your ultimate purpose seems to be able to give the name of the winner. The row number has no relevancy, it is only an intermediate result.
Work differently.
I assume that your tickets are sold in batches, say 10 tickets per batch.
Build your spreadsheet with the starting ticket number of every batch in column 1 (no need to have the ending ticket number even your batches have unequal length because this number is one less that the next starting number). You can enter the ending number in column 2 but this is not necessary.
Enter the purchaser name in column 2 or 3.
Enter a number larger than any others in the last row (stop guard).
The trick here is to sort the column-1 numbers in ascending order. For example purpose, I assume this table is in C1:C100 (3-column table allowing for 1000 tickets in batches of 10).
To display the winner’s name in a cell, enter the winning ticket in A1 and read the name in A2 with the formula =VLOOKUP(A1;C1:C100;3) where the 3 is the column number of the name in the ticket table.
To show the community your question has been answered, click the ✓ next to the correct answer, and “upvote” by clicking on the ^ arrow of any helpful answers. These are the mechanisms for communicating the quality of the Q&A on this site. Thanks!
In case you need clarification, edit your question (not an answer which is reserved for solutions) or comment the relevant answer.
Thanks very much and apologies for my slow reply. My spreadsheet was nearly as you suggested. Just had to make the start ticket number the first column in the data table (change column order). And it worked!
Our next raffle can run much more smoothly. And I learnt a bit about VLOOKUP. I had not worked out the behavior when interpolating values. But the documentation does describe it as such…
If there is no exact match, the row above where value would appear in the left column is found;