Having trouble with INDEX and MATCH

[smidge]

Hi everyone,

I have a workbook that I use to track orders for the company I work for. I have been using the INDEX and MATCH formulas to look up invoice numbers and match them to order numbers. Somehow it got messed up and I'm having trouble fixing it.

Here is the formula:
=INDEX(inv!$A$2:$B$8000,(MATCH(B11,inv!$B:$B,FALSE))+inv!$A:$A,(MATCH(B11,inv!$B:$B,FALSE))+inv!$A:$A)

But instead of returning the invoice formula like I was hoping, I get the #REF! error.

When I open the builder for the formula, everything seems to be working, so I dont understand why it's not populating.

If you have any idea why I'm getting this error and how I could fix it I would appreciate it!

Holly[/img]

 

[smidge]

I just noticed the picture is really small. I just wanted to put it there so you could see that all three lines are producing a result. I'm just not getting one in the actual cell.

Thanks again

 

[Aladin Akyurek]

The +inv!$A:$A bit is bound to cause a #REF! error.

 

[smidge]

I'm getting the error without that part too.

 

[Aladin Akyurek]

I'm getting the error without that part too.

Yes, you will. I just pointed out the most obvious error in the formula you posted. Would you post the data from A2:B10 including headers if any?

 

[smidge]

Example data:

Invoice Number Order Number
5213468 2A89Q7
5213469 2A89Q9
5213470 2A89R3
5213471 2A89R4
5213472 2A89R5
5213473 2A89R7
5213474 2A89R8
5213475 2A89R9
5213476 2A89S0
5213477 2A89S2

The headers are in A1 and B1.

 

[Aladin Akyurek]

Example data:

Invoice Number Order Number
5213468 2A89Q7
5213469 2A89Q9
5213470 2A89R3
5213471 2A89R4
5213472 2A89R5
5213473 2A89R7
5213474 2A89R8
5213475 2A89R9
5213476 2A89S0
5213477 2A89S2

The headers are in A1 and B1.

Book5
	A	B	C	D	E	F	G
1	Invoice#	Order#				Sorted Data	Unsorted Data
2	5213468	2A89Q7			Invoice	Order	Order
3	5213469	2A89Q9			5213471	2A89R4	2A89R4
4	5213470	2A89R3			5213486	Not Found	#N/A
5	5213471	2A89R4
6	5213472	2A89R5
7	5213473	2A89R7
8	5213474	2A89R8			Order	Invoice
9	5213475	2A89R9			2A89R7	5213473
10	5213476	2A89S0
11	5213477	2A89S2
12
Sheet1

Look for Invoice# and retrieve the assciated Order#.

For sorted table:

F3:

=IF(LOOKUP(E3,$A$2:$A$11)=E3,LOOKUP(E3,$A$2:$B$11),"Not Found")

For unsorted table:

G3:

=INDEX($B$2:$B$11,MATCH(E3,$A$2:$A$11,0))

Look for Order# and retrieve the associated Invoice#.

For unsorted table:

F9:

=INDEX($A$2:$A$11,MATCH(E9,$B$2:$B$11,0))

 

[smidge]

It works! Thank you so much!