Lookup within a lookup?

[odonnela]

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!

 

[Cbrine]

True enough, although I've never yet encountered a situation like that yet. If I did, then I would be posting to the board for a solution. Or be using the index-match solution.

Just to make sure: It's not an issue of Vlookup vs Index/Match. Concatenation without a separator is the issue. The errors due to concatenation without an improbable separator can easily go undedected.

Aladin,
Thanks,
I realized it after I made my response. By adding A1&" "&B1, it seperates the 2 values, so the situation you mentioned would never become an issue.

 

[Aladin Akyurek]

True enough, although I've never yet encountered a situation like that yet. If I did, then I would be posting to the board for a solution. Or be using the index-match solution.

Just to make sure: It's not an issue of Vlookup vs Index/Match. Concatenation without a separator is the issue. The errors due to concatenation without an improbable separator can easily go undedected.

Aladin,
Thanks,
I realized it after I made my response. By adding A1&" "&B1, it seperates the 2 values, so the situation you mentioned would never become an issue.

A " " (space) is not that improbable. Hence, CHAR(127).

 

[odonnela]

Thanks, all. Aladin, your formula works great. ANd I must say, I'm very impressed with your use of the CHAR(127) to avoid the concatenatin pitfall of "1 12" vs "11 2."

On a side note, I was actually trying to do the lookup on a separate workbook from the data table. When I entered the forumla, it was a little longer than a full line, and didn't see to work (in fact, when I went throgh the formula and pressed "F9" to audit it piece by piece, I got some sort of error message which said, "Error: Formula too long." I've NEVER seen that before, and I've had formulas that are four or five lones long before. Oh well. So I ran the numbers on the same sheet as the data, instead -- it worked this time. Then I just copied the results back to the other workbook. Is there a good explanation as to why the formula wouldn't work when I tried to run it from another workbook? And what's with the "Error: Formula too long" message -- it wasn't too long, and I've NEVER seen that before. Thanks again for the tremendous help!

 

[just_jon]

That must be a whopper of a path/file name!

 

[odonnela]

HELP! (one last time...)

I'm doing something similar -- long story (happy to explain if someone wants to take a crack at "doing it correctly / in the most efficient manner"). Anyway, the Index/Match combo seems to work for what I want to do (Actually, I'm just using the Match function this time around, I think).

Here's the deal -- I'm trying to copy the formula down -- my first formula reads:
=MATCH(D8&C8,Sheet2!$A$2:$A$460&Sheet2!$D$2:$D$460,0)

I pressed CTRL+ALT+ENTER so it would become an array and add the brackets ("{ }"). Now, I think, because this is an array formula, I can't copy this down -- I have to go cell by cell, hitting F2, then hitting CTRL+ALT+ENTER -- I can't copy an array.

Normally, this wouldn't be a big problem -- however, for my purposes, I want to copy this formula down 20,488 rows (yes, that many). So what do I do? Any suggestions? I also want to convert the formula to an IF(ISNUMBER()) or IF(ISERROR()) -- I just want to know if it returns a value or not -- I assume this is possible, but compounds the formula.

PLEASE HELP!!!

THANKS!!!

 

[Aladin Akyurek]

HELP! (one last time...)

I'm doing something similar -- long story (happy to explain if someone wants to take a crack at "doing it correctly / in the most efficient manner"). Anyway, the Index/Match combo seems to work for what I want to do (Actually, I'm just using the Match function this time around, I think).

Here's the deal -- I'm trying to copy the formula down -- my first formula reads:
=MATCH(D8&C8,Sheet2!$A$2:$A$460&Sheet2!$D$2:$D$460,0)

I pressed CTRL+ALT+ENTER so it would become an array and add the brackets ("{ }"). Now, I think, because this is an array formula, I can't copy this down -- I have to go cell by cell, hitting F2, then hitting CTRL+ALT+ENTER -- I can't copy an array.

Normally, this wouldn't be a big problem -- however, for my purposes, I want to copy this formula down 20,488 rows (yes, that many). So what do I do? Any suggestions? I also want to convert the formula to an IF(ISNUMBER()) or IF(ISERROR()) -- I just want to know if it returns a value or not -- I assume this is possible, but compounds the formula.

PLEASE HELP!!!

THANKS!!!

Is the Calculation set to Autamatic?

What about creating a additional column on Sheet2, using

=A2&","&D2

say in X2 on?

This would allow you to use simpler:

=MATCH(D8&","&C8,Sheet2!$X$2:$X$460,0)

If you don't want see #N/A's and the data of interest is not sorted...

1.

=IF(ISNUMBER(SETV(MATCH(D8&","&C8,Sheet2!$X$2:$X$460,0))),GETV(),"")

which requires the morefunc.xll add-in.

2.

=IF(ISNUMBER(MATCH(D8&","&C8,Sheet2!$X$2:$X$460,0)),MATCH(D8&","&C8,Sheet2!$X$2:$X$460,0),"")

 

[odonnela]

THANKS ALADIN!

 

[Jeral]

Wow, I never look at Concatention with the seperator, and what a great idea. Never ran into the trouble, but your right, needed to error trapping..

This place is awesome, learn something nev evertime I visit, just eats up all my time here..

Jeral