Use the Indirect formula with Match and Index to find data in unsorted column

[steinfm]

I am trying to figure out a way to lookup data in column B and pull the resulting data from column A. The data is not sorted. The Lookup and Vlookup commands did not work and someone suggested the index and match formula. That works when there are no variables. I want to use this formula with the indirect command. What if there is a field that identifies the month (B8), and depending on the month in that field, the formula will pull the data from the right worksheet – in this case January. I want to use the variable in column B8. Meaning, I can change the January to February and it will find the data on the February worksheet instead? I’m sure the indirect command will work, but I can’t figure out the syntax.

The Division # in A9 can be alphanumeric so a numbers based formula won’t work.

I tried =+INDEX(+INDIRECT("'"&TEXT(B8,"mmmm")&" JV'!A2:A6"),MATCH(C105,+INDIRECT("'"&TEXT(B8,"mmmm")&" JV'!b2:b6"),0)), but it doesn't work. I'm getting a "#Value!" error

Can someone help?


Column A Column B
1 Units Division
2 450 9658
3 250 1112A
4 525 5634
5 125 3251
6 350 2796CDR


7
8 RESULTS FOR MONTH OF JANUARY
9 5634 =INDEX($A$2:$A$6,MATCH(A9,$B$2:$B$6,0)) Result should be 525

[JBeaucaire]

Based on your example at the end, the formula in B9 would be:

=INDEX(A1:A6, MATCH(A9, B1:B9, 0))

You should probably upload a sample workbook manually demonstrating what you're trying to do if that formula doesn't work. I don't see the application of INDIRECT() in this text example, a workbook should allow you to demonstrate more clearly.

Click GO ADVANCED and use the paperclip icon to post up a copy of your workbook.

[steinfm]

Obiously I can't place the file out here, but I did create one in the basic form of what I want. As you can see, what I'd like the formula to do is to change which worksheet it picks up the data from. If the "Month" field changes from January to February, it should automatically pick up the data from the correct worksheet. I think the indirect.ext command will act as a variable, but I am having trouble with the syntax.

[JBeaucaire]

In C14, then copied down:

=INDEX(INDIRECT("'" & $B$2 & "'!$A:$A"),MATCH(B14,INDIRECT("'" & $B$2 & "'!$B:$B"),0))