• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Nested IF & VLOOKUP issue (solved)

Kaboom

New Member
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.
 

Attachments

  • exceltest1.xlsx
    16.1 KB · Views: 1
Last edited by a moderator:
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
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
 
Hi all,

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

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

Attachments

  • exceltest1 (1).xlsx
    16.2 KB · Views: 5
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
 
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))

82752
 

Attachments

  • MultiplyWorkbookLookup.xlsx
    22.2 KB · Views: 2
Back
Top