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
Bookmarks