=IF formula not working consistently

**kazphilips** · 06-12-2014, 12:31 PM

I have the following formulas in I2 and I3 respectively":

=IF(H2='functions DO NOT DELETE'!A:A,'functions DO NOT DELETE'!B:B,"nothing")
=IF(H3='functions DO NOT DELETE'!A:A,'functions DO NOT DELETE'!B:B,"nothing")

Both cells H2 and H3 contain the word "Manager". Both should contain the same result. The formula in I2 works and comes back with the correct result from the worksheet "functions DO NOT DELETE', but the second one, I3, along with all consecutive ones in the column, come back with the result "nothing" even though they should have the same result. Why does the first formula work but none of the rest?

Changing the formula to !A$1:A$30 and !B$1:B$30 does not change the result.

**ChemistB** · 06-12-2014, 12:52 PM

An IF statement is typically set up such that a single reference is compared to a single constant or reference and returns a single response depending on whether it's true or not. You are comparing a single reference to multiple other references and asking it to return multiple other references.

Here's how it works;
=IF(H2='functions DO NOT DELETE'!A:A,'functions DO NOT DELETE'!B:B,"nothing")

The first part H2='functions DO NOT DELETE'!A:A is the logical test.
It must return either TRUE or FALSE but you have it looking at multiple cells. Assuming H2 = manager, Excel picks the value in Column A that is in the same row as the Formula. Since your formula is in row 2, it's comparing H2 to A2. The formula in I3 is looking at A3

'functions DO NOT DELETE'!B:B is returned if the logical test is true. Do you want to return every value in column B if that is so? Are you trying to return all the corresponding values from B whenever there is "manager" in Column A? All within a single cell or copied down the column?

Any of this helping?

**kazphilips** · 06-12-2014, 02:21 PM

Sorry, I'm just not getting it. Perhaps the attached sample will help.

**Jonmo1** · 06-12-2014, 02:24 PM

Try this in I2 and filled down.

=IFERROR(VLOOKUP(H2,'functions DO NOT DELETE'!$A$2:$B$6,2,FALSE),"nothing")

**kazphilips** · 06-12-2014, 02:43 PM

My apologies...I SHOULD have known that. VLOOKUP is a basic Excel 101 function that I use every day. Thanks.

**Jonmo1** · 06-12-2014, 02:45 PM

No need to apologize, that's what the forum is for.

BTW, if you want to skip the intermediate value, and just make the formula in H return the Rank #
you can change the formula in H2 from
=IFERROR(LOOKUP(2^15,SEARCH('functions DO NOT DELETE'!A$1:A$6,G2),'functions DO NOT DELETE'!A$1:A$6),"")
to
=IFERROR(LOOKUP(2^15,SEARCH('functions DO NOT DELETE'!A$1:A$6,G2),'functions DO NOT DELETE'!B$1:B$6),"")

**kazphilips** · 06-12-2014, 02:57 PM

Thanks Jonmo1. I actually do need that intermediate value because the ranking changes from one project to the next depending on the job function.

I appreciate your help!!

**Jonmo1** · 06-12-2014, 03:19 PM

You're welcome.

=IF formula not working consistently

LinkBack

Thread Tools

Rate This Thread

Display

=IF formula not working consistently

Re: =IF formula not working consistently

Re: =IF formula not working consistently

Re: =IF formula not working consistently

Re: =IF formula not working consistently

Re: =IF formula not working consistently

Re: =IF formula not working consistently

Re: =IF formula not working consistently

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] COUNTIF not working consistently

[SOLVED] *COUNTIF with nested AND/OR -- not consistently working

[SOLVED] VLookup not working consistently

24 hour time not working in formula consistently

Sumproduct Formula Not Working Consistently - Date Issue

Bookmarks

Bookmarks

Posting Permissions