Hi,
quite a newbie here. I've got the following if formula with two vlookups however it does not work for probably obvious reasons.
=IF(VLOOKUP(A1;[Input.xls]Sheet1!$A$1:$B$5;2;0)=”OK”;”OK”;VLOOKUP(A1;[Input.xls]Sheet2!$A$1:$B$5;2;0))
This simple logic doesn't work as I see.
I suspect I need some sort of nested If or smht...
Can anyone give a quick help with this?
'files attached..
thanks in advance,
/andy.
Last edited by andy.smith; 01-25-2012 at 03:07 AM.
Hi Andy,
what do need to obtain from the formula if there is not a correspondence between the two lists?
Regards
-----------------------------------------------------
At Excelforum, you can say "Thank you!" by clicking the star icon below the post.
Please, mark your thread [SOLVED] if you received your answer.
Forum rules: http://www.excelforum.com/forum-rule...rum-rules.html
The formula used is correct. Problem is 6666 is actually not available in the input sheet.try for 5555 or 4444. It will work .
Thanks guys for looking into this.
Canapone, the attached are dummy sheets, since the originals contain some sensitive info. What i'm trying to achieve is that vlookup would check Input file>sheet1, say B1 if it contains "OK", if it does, retrieves "OK", if it does not, another vlookup should retrieve another value from Input file>sheet2.
The result should be in the Output file.
for some reason it only retrieves the result from the first vlookup, the if false condition vlookup does not work.
nadarajan, the 6666 is actually in the sheet2, the same Input file. But the formula does not work even if both sets of data are inthe same sheet/workbook.
any insights welcome.
/andy
Hi,
sure: I did not have read the formula correctly.
A possible solution:
Regards=IF(not(iserror(VLOOKUP(A1;[Input.xls]Sheet1!$A$1:$B$5;2;0)=”OK”));”OK”;VLOOKUP(A1;[Input.xls]Sheet2!$A$1:$B$5;2;0))
Last edited by CANAPONE; 01-24-2012 at 09:05 AM.
-----------------------------------------------------
At Excelforum, you can say "Thank you!" by clicking the star icon below the post.
Please, mark your thread [SOLVED] if you received your answer.
Forum rules: http://www.excelforum.com/forum-rule...rum-rules.html
What other value?Originally Posted by andy.smith
Good luck.
Canapone, the code does not work for me, sorry. Is it something to do with me using excel 2002? It says formula contains an error and highlightes "OK".
OnErrorGoto0, the other value in sheet2 will be "OK" as well or blank. The purpose of this is that data from sheet1 would not loose it's "OK" status when moved to sheet2. And it needs actively check if it is still "OK", hence there is a need for a second vlookup to constantly retrieve the value of the particular cell.
I hope that makes sence..
p.s. the value for the first vlookup can be "OK" or blank
/andy.
Sorry, I have not noticed the sheet2, Use this formula.
=IF(ISERROR(VLOOKUP(A6,[Input.xls]Sheet1!$A$1:$B$5,2,0)),VLOOKUP(A6,[Input.xls]Sheet2!$A$1:$B$5,2,0),VLOOKUP(A6,[Input.xls]Sheet1!$A$1:$B$5,2,0))
This will work I think
What do you want to return if the value is not found in either sheet? Or is #N/A acceptable? If so, nadarajan's formula should suffice.
Good luck.
Canapone, the code does not work for me, sorry. Is it something to do with me using excel 2002? It says formula contains an error and highlightes "OK".
OnErrorGoto0, the other value in sheet2 will be "OK" as well or blank. The purpose of this is that data from sheet1 would not loose it's "OK" status when moved to sheet2. And it needs actively check if it is still "OK", hence there is a need for a second vlookup to constantly retrieve the value of the particular cell.
I hope that makes sence..
p.s. the value for the first vlookup can be "OK" or blank
/andy.
Re: Two Vlookups do not work with IF. Why?
=IF(ISERROR(VLOOKUP(A1,Sheet2!$A$1:$B$5,2,FALSE)),VLOOKUP(A1,Sheet1!$A$1:$B$5,2,FALSE),VLOOKUP(A1,Sh eet2!$A$1:$B$5,2,FALSE))
Last edited by chetanmehra1989; 01-24-2012 at 10:56 AM. Reason: issue
the suggested formulas arn't working fully..
nadarajan, for some reason only sheet1 retrieves OK, while from sheet2 it retrieves "0". any ideas how to fix it?
NA for blanks is ok for me.
thanks for your patience with this.
/andy.
Last edited by andy.smith; 01-24-2012 at 01:52 PM.
I think you will require something like this
=LOOKUP(REPT("Z",255),CHOOSE({1,2},VLOOKUP(A6,[Input.xls]Sheet1!$A$1:$B$5,2,FALSE),VLOOKUP(A6,[Input.xls]Sheet2!$A$1:$B$5,2,FALSE)))
which will return either 'OK' or N/A assuming the lookup cells are either blank or OK.
Good luck.
OnErrorGoto0, this is brilliant! Worked like a charm. Thanks everyone for your time and efforts.
/andy.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks