Excel 2007 : IF function

**stojko89** · 05-26-2009, 02:23 AM

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

**oldchippy** · 05-26-2009, 02:57 AM

Hi,

Take a look at VLOOKUP here

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

**stojko89** · 05-26-2009, 06:45 AM

doesnt realy help...

**modytrane** · 05-26-2009, 07:43 AM

post a sample workbook with expected results and some explanation.
modytrane

**stojko89** · 05-27-2009, 02:33 AM

ok so this is the sample and inside it i wrote what i wish to be made

Please if anyone can, help.

**modytrane** · 05-27-2009, 09:32 AM

Your post and question are still not clear.

Your request:

HTML Code:

E2 on List2 is Material and there is nbo matching column on sheet1.
There is no "row A". Did you mean column A?.
You want to compare five values from List2 with what five values/text on list1?

Give a specific example with result. Enter the expected result manually and describe the logic to get that result.

I am sure it's clear in your mind, what you want, but we can't read your mind, so you need to help us understand.

modytrane.

**oldchippy** · 05-27-2009, 03:35 PM

Hi,

I'm assuming you are looking for a VLOOKUP so I've put one on Sheet2 that has an actual matching number that is on Sheet1 so you can see how it works

**stojko89** · 05-28-2009, 12:10 AM

oldchippy you all most have it, its the oposit of this, im not looking for those 4 values to be VLOOKUP im looking for the colum $Šifra$ to be VLOOKUP under those 4 values
I tryed this:

Please Login or Register  to view this content.

**modytrane** · 05-28-2009, 12:45 PM

OK,
Here's a sort of crude solution, but it's simle and it works.
See the atccahed file.
On List1, I've created a column A and on List2 I added column R.
You can hide both of these columns.
Now look at the formula in C21 on List2 sheet.
You can drag that formula up and down that column.
Also, drag formula in col. A[List1] and R[List2].
Hope it works for you.

You can try it out by entering different values in E21 thru' H21 on List2 and see C21 give you the right answer.
modytrane.

**stojko89** · 05-29-2009, 12:03 AM

Ok so it is posible and works so tnx.
I'm still trying to make something witouth help cells hehe. but ill use this now
It realy helps a lot ty.

**DonkeyOte** · 05-29-2009, 01:15 AM

Non-Helper Formula based on example

Please Login or Register  to view this content.

**stojko89** · 05-29-2009, 01:39 AM

tnx a lot ppl

**modytrane** · 05-29-2009, 08:19 AM

DO,
That formuls is new to me. I see that it works, but I don't understand it.
Do you mind explaing how that VLOOKUP works.
Is it something new with XL2007?
I am usig XL2003 and I don't see that format explained under Help.
Thanks,
modytrane

**DonkeyOte** · 05-29-2009, 08:58 AM

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

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

The LOOKUP works along the lines of:

Please Login or Register  to view this content.

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

Our lookup_vector in this case is populated by

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

which obviously evaluate to

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

will then in turn generate our three value lookup_vector of:

Please Login or Register  to view this content.

which when evaluated further would be seen as:

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

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.

**modytrane** · 05-29-2009, 10:22 AM

DO,
Thank you very much.
That explanation is perfect. It makes sense and it's to the point.
I really appreciate your time and valuable contribution.
modytrane

**Phil_V** · 07-06-2009, 09:04 AM

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!

Excel 2007 : IF function

LinkBack

Thread Tools

Rate This Thread

Display

IF function

re: IF function

re: IF function

re: IF function

Re: IF function

Re: IF function

Re: IF function

Re: IF function

Re: IF function

Re: IF function

Re: IF function

Re: IF function

Re: IF function

Re: IF function

Re: IF function

Re: IF function

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions