Lookup, index, match ,address ??????

Hello James, for your example try this formula in C19

=LOOKUP(2,1/((F2:F10=LEFT(C18,6))*(G2:G10=RIGHT(C18,2))),A2:A10)

another way would be using this formula

=INDEX(A2:A19,MATCH(C18,F2:F10&G2:G10,0))

Although this second one is shorter it needs to be "array entered", i.e. confirmed with CTRL+SHIFT+ENTER so that curly braces appear around the formula in the formula bar.

For your example both formulas will give the same result, although, theoretically they could give differeent results if you had 2 rows which matched the criteria (LOOKUP version gives the last matching row, INDEX/MATCH the first......)

Edit: also an extra INDEX function can be used in the 2nd version.....this just obviates the need for CSE, i.e.

=INDEX(A2:A19,MATCH(C18,INDEX(F2:F10&G2:G10,0),0))

Daddy your awsome !

This is the second time I've seen the LOOKUP(2,1/ used and it makes no sense to me at all also the * in the middle says maths to me but I guess its nothing to do with multiplication.. I've also seen DonkeyOte use an & symbol which I also don't get.... I kinda see wear your going with INDEX(MATCH(INDEX - inspired! but I'm not here just for a copy /paste answer - I really want to get to grips with stuff like the lookup function VLOOKUP seems to be a walk in the park when I compare it to they things you guys do with LOOKUP but maybe I'm just not exploiting its full potential? Excel doesn't offer much help on the syntax front as far as I can see when it come to LOOKUP(2,1/(...)* OR &( ...) etc Is their any "easy" way these kind of function can be broken down ?? - Thanks so much - James

Simple one first.....

Using & in F2:F10&G2:G10 is just concatenating the data as you suggested, it's just that doing it this way you concatenate within the formula only rather than having to use an extra column on the worksheet.

The LOOKUP version is a little more complex.....

The * is indeed multiplication, when you have this test:

F2:F10=LEFT(C18,6)

it generates an array of TRUE/FALSE values, i.e. for your example

{TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE}

and for G2:G10=RIGHT(C18,2) you get

{TRUE;TRUE;TRUE;FALSE;TRUE;FALSE;TRUE;TRUE;TRUE}

When these are multiplied TRUE is co-erced to 1 and FALSE to 0, and when the arrays are multiplied that means you get zeroes unless both elements are TRUE, in which case you get 1, so multiplying those gives

{1;0;0;0;0;0;0;0;0}

[notice the 1 is for the only row where both conditions are satisfied]

Placing this in the LOOKUP formula I suggested we now have it simplified as far as this

=LOOKUP(2,1/{1;0;0;0;0;0;0;0;0},A2:A10)

When 1 is divided by an array of 1/0s then you get an array of 1/#DIV/0!s, i.e.

=LOOKUP(2,{1;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!},A2:A10)

Now the next part exploits a "flaw" in LOOKUP. When you lookup a value (in this case 2) which is greater than every number in the array it matches with the last number (it should match with the greatest number but because the lookup array is meant to be sorted ascending excel assumes that the greatest number is the last).

Of course in this case there's only 1 number (the errors are ignored) and the match is with the single 1 (as I said in my first post, if there are multiple matching rows then LOOKUP will return the last).

This is the vector form of LOOKUP (see Excel help) whereby the lookup value matches within the first vector and the corresponding value is returned from the second, in this case that means that the match is with the 1 which is the first element of the first vector, so the returned value is the first element of the second vector, i.e. A2:A10 so A2 is returned......