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?

 

[jvought]

All numbers are formatted as text.

 

[jvought]

=IFERROR(INDEX(TicketList!A2:A6,MATCH(B2,TicketList!A2:A5,0)),INDEX(TicketList!A2:A6,MATCH(B2,TicketList!A2:D5,0)))

 

[Fluff]

You are indexing the wrong column. Look at what I suggested.

 

[jvought]

I fixed that part. is there a place that I should have a ,3 in the formula to return a name?

=IFERROR(INDEX(TicketList!A2:A6,MATCH(B2,TicketList!A2:A5,0)),INDEX(TicketList!A2:A6,MATCH(B2,TicketList!B2:B5,0)))

 

[Fluff]

You are still indexing the wrong column. If you look at the formula I posted you will see that.

 

[jvought]

I changed the index and it worked.

=IFERROR(INDEX(TicketList!C2:C5,MATCH(B2,TicketList!A2:A5,0)),INDEX(TicketList!C2:C5,MATCH(B2,TicketList!B2:B5,30)))

Thank you for all your help!

 

[Fluff]

Glad to help & thanks for the feedback.