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.

	A	B	C
1	Definitive	4	4
2	Influential	3	3
3	Not Influential	1	1
4	Somewhat Influential	2	2
5	#N/A		#N/A

<tbody>
</tbody>

 

[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

Somewhat Influential	2
Somewhat Influential	2
Influential	3
N/A	3

<colgroup><col><col></colgroup><tbody>
</tbody>

 

[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}),""),"")