Using VLOOKUP & IF together

[thelauncher]

Hi all,

I'm having trouble trying to figure out a formula using IF and Vlookup. Basically what I need is to lookup a value from sheet1 that should be somewhere in sheet2. Once vlookup has located this value it then needs to go a certain amount of columns accross eg 10 in sheet2 to find another value (as a normal vlookup does). Now I cant figure it out: IF the value found in sheet2 (from the lookup) matches a value in sheet1 then I need the formula to return a 1, if it does not match then I need it to return a 0. I cant quite figure out how the formula would go, with nested functions etc

_________________________________________________________________

So in the attached on sheet1 cell b2: A vlookup looks for reference "1111" (shown in sheet1 cell A2) goes to sheet2 and finds it, then moves across the columns (sheet2 Columns E:K) until it finds the value shown in Sheet1 cell B1 ("MON"). IF it finds the match then the formula returns "1" if it does not (ie only finds other days of week or a blank cell) then the formula returns a "0".

Note The attached is excel 2007 but I need to use 2003 at work.

If anyone could assist that would be great as its driving me mad

launcher

[teylyn]

Hi,

if I understand your requirements correctly, try this formula in B2 of sheet 1 and copy down and right

=IF(VLOOKUP($A2,Sheet2!$A$2:$K$6,COLUMN()+3,FALSE)=B$1,1,0)


hth

[ibexcel]

this is the formula he needs but there is one problem with this. The formula is getting solved in such a way that in the end, it is looking for a range within sheet1 instead of sheet2, how can that be corrected ?

=COUNTIF(OFFSET(Sheet1!$A$2;MATCH($A2;Sheet1!$A$2:$A$6;0);4;1;7);B$1)

[ibexcel]

sorry, i corrected the formula now. this should solve your problem:
=COUNTIF(OFFSET(Sheet1!$A$2;MATCH($A2;Sheet1!$A$2:$A$6;0);4;1;7);B$1)

[ibexcel]

=COUNTIF(OFFSET(Sheet2!$A$2;MATCH($A2;Sheet1!$A$2:$A$6;0);4;1;7);B$1)

[teylyn]

ibexcel,

your (final) formula comes to the same result, but through the use of OFFSET is volatile, which the VLOOKUP is not.

Also, as long as there have been no further replies to a post, please feel free to use the Edit button to correct errors in your posts, instead of putting corrections in new posts.

cheers

[thelauncher]

Hi teylyn and ibexcel,

Thankyou for your responses

I went with the vlookup formula (as I have used this type of formula before) rather than the countif/offset (which didnt seem to work - excel didnt like it plus i have not used it before).

The vlookup/if works but I have not seen the COLUMN function before. Can you tell me how it works within this particular formula? Im not sure how "COLUMN()+3" part of it works with the other functions. From what I know Column just finds the column number of a cell but how does it work with IF, VLOOKUP etc in this formula?

Also just a general excel question, is there a partcular rule I can follow when nesting functions within functions ie what order they need to go wihtin a formula (eg in this formula IF was furthest out then vlookup, then column etc)

many thanks

launcher

[teylyn]

Hi,

the COLUMN() formula will return the column number of the specified cell when used like =column(a1), and it will return the column number of the cell where the formula resides if it is used without a parameter like this: =column().

In the formula ...

=IF(VLOOKUP($A2,Sheet2!$A$2:$K$6,COLUMN()+3,FALSE)=B$1,1,0)

... column establishes the column number of the current cell and adds 3 to arrive at the lookup column for VLOOKUP. In your lookup table, the result values are three columns further right than on sheet2, so that seemed to be the easiest option to make a formula that can be copied not just down, but also across.

With regards to how to nest formulas, it really depends on what you need to do. If a formula uses parameters, oftentimes these parameters can be calulated with a formula, so that is where you nest it.


hth

[martindwilson]

i think columns() is a better option tho!
=IF(VLOOKUP($A2,Sheet2!$A$2:$K$6,COLUMNS($A$1:E1),FALSE)=B$1,1,0)
COLUMNS($A$1:E1)=5 and when dragged across =6,7,8....... so you can put that anywhere on sheet 1 and it will still work just a matter of preference but it saves on having to adjust the +3 or whatever and also still works if you insert a column before your formula

[teylyn]

Nice, Martin! Have to look into that a bit more.

[martindwilson]

i just saw shg and others using it and followed suit!

[teylyn]

Even more reason to investigate this alternative. shg rules!!