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.

 

[Aladin Akyurek]

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.

LOOKUP should be invoked here if the first array is in ascending order. Try this instead:

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

 

[JudithP]

Re: HELP! LOOKUP and IFERROR Question

LOOKUP should be invoked here if the first array is in ascending order. Try this instead:

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

Hi Aladin,
It works!! Thank you. Glad to be part of this online forum. I'm new to excel so its a working progress.
Will you be able to walk me through your logic? This will help me learn and understand the concepts better.

 

[dreid1011]

Re: HELP! LOOKUP and IFERROR Question

Hi Aladin,
It works!! Thank you. Glad to be part of this online forum. I'm new to excel so its a working progress.
Will you be able to walk me through your logic? This will help me learn and understand the concepts better.

Index / Match works like a Lookup function except that the lookup range doesn't have to be sorted.

The MATCH(AQ8,{"Definitive","Influential","Not Influential","Somewhat Influential"},0) is looking up the value of AQ8 within the set values of {"Definitive","Influential","Not Influential","Somewhat Influential"}, and returning the position number if it finds a match. The 0 at the end tells it to find an Exact Match.
ie: Influential would return the number 2 as it is the second value in the list.

INDEX({4,3,1,2} ... ) then takes the 2 returned by Match, and returns the 2nd value in the set {4,3,1,2}.

ie: INDEX({4,3,1,2},2) would return 3.

All wrapped up in a nice IFERROR() to provide a blank if a match is not found.

 

[Aladin Akyurek]

Re: HELP! LOOKUP and IFERROR Question

Hi Aladin,
It works!! Thank you. Glad to be part of this online forum. I'm new to excel so its a working progress.
Will you be able to walk me through your logic? This will help me learn and understand the concepts better.

You are welcome.

1. MATCH of the MATCH bit compares AQ8 with values in the list {"Definitive","Influential","Not Influential","Somewhat Influential"}. The comparison is exact, indicated by the 0 argument. If AQ8 = Influential, MATCH finds it in this list where it's 2nd item. This 2 is then fed to the surrounding INDEX.

2. INDEX looks for the 2nd item because it's fed with 2 by MATCH in its own list, i.e. {4,3,1,2}. Since the 2nd item is 3, it returns 3 as result.

Hope this helps.