This is a different, more advanced, twist on a question I asked a few weeks ago. I'm curious if there is a way to look up values within a range, when the range is unspecified. It has to do with my "Offset Lookup" question -- here's the problem I posed before: http://www.mrexcel.com/board2/viewtopic.php?t=73889&highlight=
Here's the new twist: Suppose I have two sheets again -- a Data sheet and an Output sheet. The data sheet looks something like this:
Type / # / Grade
1 / 75 / Z
1 / 25 / Y
1 / 50 / X
2 / 25 / W
2 / 50 / V
2 / 75 / U
2 / 100 / T
3 / 50 / S
3 / 25 / R
3 / 75 / Q
Sorry the columns are somewhat hard to see -- basically, I have three columns and nine rows of data, with a header on top. So here's what I want to do -- I want to be able to have excel return the grade of Type 2, #75 (="U"), or Type 3, #25 (="R"), or I want it to tell me that there is no Type 1, #100. This is simple a lookup within a lookup. Sometimes the order of the "Types" may be mixed around -- I'm just curious if there's a way to lookup within a lookup to have excel look at two things: for example, is there a Type 2 in the list? If no, return "NA," if yes, is there a #50 within the Type 2's? If no, return "NA," if yes, return the Grade (e.g. "V").
Hopefully this is a erlatively easy cencept to understand. If someone does have an answer for the looku within the lookup, I have one more question -- do I have to sort by type? In other words, do all of my types (e.g. 2's) have to be together, or even in ascending order? Or could I mix the rows around and have excel see if there is, in fact a Type 2 with a #50 somewhere in the list.
Thanks for your help!
Here's the new twist: Suppose I have two sheets again -- a Data sheet and an Output sheet. The data sheet looks something like this:
Type / # / Grade
1 / 75 / Z
1 / 25 / Y
1 / 50 / X
2 / 25 / W
2 / 50 / V
2 / 75 / U
2 / 100 / T
3 / 50 / S
3 / 25 / R
3 / 75 / Q
Sorry the columns are somewhat hard to see -- basically, I have three columns and nine rows of data, with a header on top. So here's what I want to do -- I want to be able to have excel return the grade of Type 2, #75 (="U"), or Type 3, #25 (="R"), or I want it to tell me that there is no Type 1, #100. This is simple a lookup within a lookup. Sometimes the order of the "Types" may be mixed around -- I'm just curious if there's a way to lookup within a lookup to have excel look at two things: for example, is there a Type 2 in the list? If no, return "NA," if yes, is there a #50 within the Type 2's? If no, return "NA," if yes, return the Grade (e.g. "V").
Hopefully this is a erlatively easy cencept to understand. If someone does have an answer for the looku within the lookup, I have one more question -- do I have to sort by type? In other words, do all of my types (e.g. 2's) have to be together, or even in ascending order? Or could I mix the rows around and have excel see if there is, in fact a Type 2 with a #50 somewhere in the list.
Thanks for your help!
Or be using the index-match solution.
