Search Multiple Columns for One Value

[jvought]

Excel Version 2019

I belong to an organization that sell tickets for fundraising. Each ticket has two numbers printed on it. The first number is between 000 and 499 and the second number is a random number between 500 and 999.

I've created a sheet (TicketList) that sorts the tickets in column A containing numbers 000 through 499, column B containing the random number between 500 and 999, column C containing the ticket purchaser's name, and column D containing the purchaser's phone number.

The winning numbers are determined by to evening state lottery. On another sheet (Winners), I have the column A with days of the month. I'd like to be able to enter the winning number in column B and perform a search in TicketList columns A and B to have the winner's name and address automatically fill in columns C and D. I have tried this using index/match but it doesn't seem to work.

=INDEX(TicketList!A6:D505,MATCH(B5,TicketList!A6:B505,0),3) to get the winner's name in Winners!C5
=INDEX(TicketList!A6:D505,MATCH(B5,TicketList!A6:B505,0),4) to get the winner's phone number in Winners!D5

I'm also not sure if the numbers in both sheets have to be in a particular format. If I use anything except Text for a format, leading zeros are truncated. Everything I have tried gives me #N/A.

Is there anything obvious I'm missing with this?

 

[Fluff]

Hi & welcome to MrExcel.
Try

=iferror(INDEX(TicketList!C6:C505,MATCH(B5,TicketList!A6:A505,0)),INDEX(TicketList!C6:C505,MATCH(B5,TicketList!B6:B505,0)))

 

[jvought]

Hi & welcome to MrExcel.
Try

=iferror(INDEX(TicketList!C6:C505,MATCH(B5,TicketList!A6:A505,0)),INDEX(TicketList!C6:C505,MATCH(B5,TicketList!B6:B505,0)))

 

[jvought]

Thanks for getting back to me. I get a Value Not Available error when I tried this.

 

[Fluff]

What do you mean by "A value not available"?

 

[jvought]

I got this error so I created a new mini-workbook from scratch and still got the error.

Here is the new sheet-

Here is the error I get-

 

[Fluff]

Make sure that the value in B2 is text & not a number.

 

[jvought]

 

[Fluff]

Ok, you also need to change the formula the way I showed, you still have multiple columns for the index & not one.

 

[jvought]

I'm now getting this result when I enter 000 - 003. When I enter 004, 005, or any number from column B I get this...

However, when I enter 763 I get this...