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 in sheet2 in this cell 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

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

launcher

Hi,
I think this formula solves it for you (translated, I hope it is correct).

=IF(ISERROR(VLOOKUP(A6;data;1;FALSE));0;1)

See attachment for an example.

/Bjorn

Attached Files

example1.xls (13.5 KB, 47 views)

Hi launcher,

Instead of guessing at what you mean by "a certain amount" and providing an inaccurate solution, would you please post a sample spreadsheet?

Cheers,

sure bro post a sample and we help u out there and then

have you tried a countif formula. It will look if something exists on sheet 2 from what is on sheet 1. =countif('sheet2'!A:A,'sheet1'!a1)

This will count how many times that record exists. as far as nesting formulas it is easy to get comfortable with this by watching the tooltips.

For example when writing an if statement you start with =if(
now the tool tips will tell you that you are writing the condition. then you press a comma and it asks for what you want if the condition is true. You can put another formula in here if you want. try a sum write =if(a1=9,sum(6+3),sum(6-3))

Notice the last parentheses to close off the original if formula. It is very good to get comfortable with nesting functions. give it a shot and let us know what you come up with.

4 year old thread......

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post