Nested IF & VLOOKUP issue (solved)

[Kaboom]

Hello Everyone,

I need assistance please

Problem:
VLOOKUP isn't working in the [FALSE] section of nested IF.

Scenario:
I have a number of sheets in a workbook listing companies using different tech. (eg: sheet 2 has companies using Shopify, Sheet 3 lists companies using Magento...etc.
Column A in each sheet has the company name.

I am using Sheet 1 as a Master, listing all companies.
Column A named Company Name (with all companies listed),
Column B called "Tech" to list each platform referenced from each sheet

Have attached example file

Thanks in advance for any assistance.

 

[bosco_yip]

Welcome to the board.

Please don't post formula that is not worked for you.

And forward us a sample file with minimum 5 rows of source data,

Tell us, what do you want? complete with requirements, criteria and expected result.

Thank you

 

[Kaboom]

Welcome to the board.

Please don't post formula that is not worked for you.

And forward us a sample file with minimum 5 rows of source data,

Tell us, what do you want? complete with requirements, criteria and expected result.

Thank you

Welcome to the board.

Please don't post formula that is not worked for you.

And forward us a sample file with minimum 5 rows of source data,

Tell us, what do you want? complete with requirements, criteria and expected result.

Thank you

I have uploaded sample file

 

[Kaboom]

Hi all,

I worked it out myself - YAY!! - using IFERROR

Have attached file with the corect code if anyone else comes across the issue

 

[pecoflyer]

Fine you found a solution and thanks for sharing

However, the use of VLOOKUP in a one column range can be simply replaced using the MATCH function to make your formula more readable,
like =IFERROR(IF(MATCH($A2,Shopify!A:A;0),"Shopify"),IFERROR(IF(MATCH($A2,Magento!A:A,0),"Mag"),IFERROR(.......
(and there probably are shorter solutions still)

And IMO, using entire column references is bad practice. Excel Tables will do the job and adapt to increasing or decreasing ranges

 

[bosco_yip]

Hi all,

I worked it out myself - YAY!! - using IFERROR

Have attached file with the corect code if anyone else comes across the issue

Please refer to the Example 1 of which you solved it using IF function.

1] And I proposed to use a 3D formula as show in Example 1:

It required to set up a "Sheet name List" (H1:H5) and "Lookup result list" (I1:I5)

Then, in C2 copied down formula:

=INDEX($I$2:$I$5,MATCH(1,1/COUNTIFS(INDIRECT("'"&$H$2:$H$5&"'!$A$2:$A$10"),$A2),0))

2] My formula can use, if the Company name is not in order as per Example 2

Please see the formula used in F2:

=INDEX($I$2:$I$5,MATCH(1,1/COUNTIFS(INDIRECT("'"&$H$2:$H$5&"'!$A$2:$A$10"),$E2),0))

 

[Kaboom]

Excellent suggestion thats for the response

 

[bosco_yip]

@Kaboom

We all welcome you join in.

Hoping you can step in further new world of Excel, and we all in Excelling by this group.

Regards
Bosco