Excel 2007 : IF function

So what im trying to do is:

i have 5 rows on sheet2 and 5 rows in sheet1 and what im trying to do is make condisions like this:

If velues in rows B2,C2,D2,E2 on sheet2 are the same as the values in rows somewhere in the B,C,D,E (has to be the same row number like(B5,C5,D5,E5) on sheet1 then copy or vlookup or match value from A(from say A5) from sheet1 to row on to sheet2 (like B1).

I Tryed using IF function if,if,if,if,if but all i got was FALSE

Please I need this. Then i'll be quiet for a while

Hi,

Take a look at VLOOKUP here

http://www.contextures.com/xlFunctions02.html

I will illustrate the LOOKUP approach with a simpler example...

First off, LOOKUP is an old formula rather than a new - I've heard people discussing that LOOKUP is nothing more than a legacy function which has been usurped by the likes of VLOOKUP/HLOOKUP etc and thus serves no purpose .. I've had to interject!
It is in fact a very useful function, far more so than either of V/HLOOKUP (both of which can be replaced easily with INDEX/MATCH)

Consider

A1:A3: 1, 1, 2
B1:B3: A,B,A
C1:C3: 100,200,300

E1: 2 (for use as criteria re: A1:A3)
F1: A (for use as criteria re: B1:B3)

In G1 I want to get the value from C where A1:A3 = E1 and B1:B3 = F1, I can do this using LOOKUP as so:

G1: =LOOKUP(2,1/(A1:A3&B1:B3=E1&F1),C1:C3)

The LOOKUP works along the lines of:

LOOKUP(lookup_value,lookup_vector,[result_vector])

The result_vector is optional (denoted by [ ]) - more later...

Our lookup_vector in this case is populated by

1/(A1:A3&B1:B3=E1&F1)

the section within parentheses will generate a Boolean TRUE / FALSE depending on whether the string concatenation of Ax&Bx = E1&F1, so we will end up with three booleans (rows 1:3)... using our example data:

{"1A"="2A","1B"="2A","2A"="2A"}

which obviously evaluate to

{FALSE,FALSE,TRUE}

We know that dividing a Boolean will coerce it to it's integer equivalent where TRUE = 1 and FALSE = 0. As a result of which we know then that the:

1/(Boolean)

will then in turn generate our three value lookup_vector of:

{1 / 0, 1 / 0, 1 / 1}

which when evaluated further would be seen as:

{#DIV/0!,#DIV/0!,1}

It's important at this point to note that using this 1/Boolean approach any value in our lookup_vector can only ever be one of 2 values - either a 1 or a #DIV/0!

By setting our lookup_value to 2 we ensure that our lookup_value is always bigger than any of the values in our lookup_vector (the max is 1 as we've discussed). In short (?) LOOKUP utilises the Binary Search Algorithm* at all times unlike VLOOKUP etc where it is optional - the Binary Search Algorithm being that employed by VLOOKUP when the final parameter is set to TRUE rather than FALSE ... it (Binary Search) is much quicker than conducting exact matches but it does require sorted data... in our case the data is not sorted but by setting our lookup_value to be bigger than any value in our lookup_vector we ensure that LOOKUP returns the last value from the lookup_vector that is of the same data type as the lookup_value - this is key - in our case the lookup_value is a number (2) of course.... so this means:

LOOKUP(2,{#DIV/0!,#DIV/0!,1})

will return 1 as the errors are quite simply ignored - this is the real beauty of LOOKUP, errors within the lookup_vector do not cause the formula to fail.

Obviously 1 is not really very useful... but LOOKUP as we know has an optional parameter [result_vector] ... so here we can get LOOKUP to return an associated value to our lookup_vector, eg:

LOOKUP(3,{1,2,3},{"a","b","c"})

will return "c" as "c" is the result vector value associated with 3 in the lookup_vector (as "a" is tied to 1 and "b" to 2 etc...)

So by setting our result_vector to C1:C3 we can return the value we want based on where we found 1 in our loookup_vector

LOOKUP(2,{#DIV/0!,#DIV/0!,1},{100,200,300})

So the formula returns 300.

I hope that makes some sense... it's not the easiest thing to explain.


*I won't try to explain the Binary Search Algorithm because put simply I'm not clever enough and others who are already have online etc... so you will be best served googling for more info on the particulars.

Just wanted to say that I've just read this through after being referred here from another thread, and D.O that is a great explanation, it has really cleared up the mystery of how that was working for me

Thanks again!