LOOKUP and IFERROR Question

[JudithP]

Hi Excel Community,
My function is set to not count any "N/As" but it's not working. N/As are being counted as 3. The likert scale of 3 should only be returned when "influential" is stated in the column.

Here is the function I typed in:

=IFERROR(LOOKUP(AQ8,{"Definitive","Influential","Not Influential","Somewhat Influential"},{4,3,1,2}),"")

If anyone can help, it would be much appreciated.

 

[dreid1011]

Re: HELP! LOOKUP and IFERROR Question

Hi Excel Community,
My function is set to not count any "N/As" but it's not working. N/As are being counted as 3. The likert scale of 3 should only be returned when "influential" is stated in the column.

Here is the function I typed in:

=IFERROR(LOOKUP(AQ8,{"Definitive","Influential","Not Influential","Somewhat Influential"},{4,3,1,2}),"")

If anyone can help, it would be much appreciated.

Welcome to the board. Could you post a small sample of data and expected results? Are the strings in the lookup array results of formulas? And what exactly is in AQ8?

 

[dreid1011]

Re: HELP! LOOKUP and IFERROR Question

Here is the sample I generated and all seems to be working as intended...

Formula in B2 and copied down: =IFERROR(LOOKUP(A2,{"Definitive","Influential","Not Influential","Somewhat Influential"},{4,3,1,2}),"")
Formula in C2 and copied down: =LOOKUP(A2,{"Definitive","Influential","Not Influential","Somewhat Influential"},{4,3,1,2})

The "#N/A" in A5 was typed manually.

[TABLE="width: 259"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[/TR]
[TR]
[TD="align: right"]1
[/TD]
[TD]Definitive
[/TD]
[TD="align: right"]4
[/TD]
[TD="align: right"]4
[/TD]
[/TR]
[TR]
[TD="align: right"]2
[/TD]
[TD]Influential
[/TD]
[TD="align: right"]3
[/TD]
[TD="align: right"]3
[/TD]
[/TR]
[TR]
[TD="align: right"]3
[/TD]
[TD]Not Influential
[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]1
[/TD]
[/TR]
[TR]
[TD="align: right"]4
[/TD]
[TD]Somewhat Influential
[/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"]2
[/TD]
[/TR]
[TR]
[TD="align: right"]5
[/TD]
[TD="align: center"]#N/A
[/TD]
[TD][/TD]
[TD="align: center"]#N/A
[/TD]
[/TR]
</tbody>[/TABLE]

 

[FDibbins]

Re: HELP! LOOKUP and IFERROR Question

Agree that it is hard to troubleshoot with out seeing some related data?

 

[JudithP]

Re: HELP! LOOKUP and IFERROR Question

Agree that it is hard to troubleshoot with out seeing some related data?

Hi Everyone,
Thank you for responding so quickly. Here is a sample of my data set. I'm trying to use the formula to make the texts into a number value. If you can see below N/A becomes 3. 3 should only be inserted when "influential" is mentioned. This affects my average.
I hope this helps and y'all can provide some feedback.
Column 1 column 2
[TABLE="width: 623"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Somewhat Influential[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Somewhat Influential[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Influential[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]N/A[/TD]
[TD="align: right"]3[/TD]
[/TR]
</tbody>[/TABLE]

 

[JudithP]

Re: HELP! LOOKUP and IFERROR Question

Sorry. Column 2 should be above the 2.

 

[FDibbins]

Re: HELP! LOOKUP and IFERROR Question

Im not following what the problem is?
using your formula...
=IFERROR(LOOKUP(A2,{"Definitive","Influential","Not Influential","Somewhat Influential"},{4,3,1,2}),"")
gives a "" for a non-match? Is that not what you wanted?

 

[FDibbins]

Re: HELP! LOOKUP and IFERROR Question

hmm maybe if you swap the criteria around?
=IFERROR(LOOKUP(A2,{"Definitive","Influential","Somewhat Influential","Not Influential"},{4,3,2,1}),"")
Not sure that will change anything though

 

[JudithP]

Re: HELP! LOOKUP and IFERROR Question

Hi Fdibbins,
In short, N/A becomes 3. It shouldn't be counted as 3. 3 means "influential". I changed the word influnteial into 3 because I need to find the average responses. The data is from a survey.

 

[dreid1011]

Re: HELP! LOOKUP and IFERROR Question

Hi Fdibbins,
In short, N/A becomes 3. It shouldn't be counted as 3. 3 means "influential". I changed the word influnteial into 3 because I need to find the average responses. The data is from a survey.

I see what is happening. I can't tell you why the Lookup is treating "N/A" as a match or near match for "Not Influential", but it's not the error value "#N/A" either.

Try this:

=IFERROR(IF(A2<>"N/A",LOOKUP(A2,{"Definitive","Influential","Not Influential","Somewhat Influential"},{4,3,1,2}),""),"")