Complex VLOOKUP - Identifying values used in different spreadsheet - combinations :)

Hi guys, can someone help me with the following problem:

I have a single workbook. This currently contains 4 spreadsheets. Blue, Red, Green and White.

1. I have a single workbook
2. This currently contains 4 spreadsheets (Blue, Red, Green and White.)
3. The Blue, Red and Green spreadsheets have the same layout. The columns of interest are column C which is a target code, column D is a target description)
4. The White spreadsheet is the master list and contains column A which is the code and B which is the description.
5. The Blue, Red and Green tabs have a formula in place in their target description columns (D) that looks at its code in column C and compares this to the one in the White tab (column A) and then imports the corresponding description to the code from the White tab (column B) if present.

I have added an example below:
Attachment 434363

The problem I have:

1. In the White tab I want to add the ability to see if the value is used in either the Red (column C), Blue (column C) or Green tabs (column C) and return an indicator based on the below:
2. So for example: If the value is only used in the Blue tab (matching value in Blue!C:C then indicate ‘blue’ in cell D2 on the White tab.
3. If only the Red tab: indicate ‘red’ in cell D2
4. If only the Green tab: indicate ‘green’ in cell D2
5. If in Blue and Red but not green then ‘Blue and Red’ in cell D2
6. If in Blue and Green but not Red then ‘Blue and Green’ in cell D2
7. If in Red and Green but not Blue then ‘Red and Green’ in cell D2

I believe the VLOOKUP function is required nested in an IF statement.

Could someone be kind enough to point me in the right direction.

I appreciated any help what-so-ever

Thanks,

Sam

That's not the same as attaching a workbook.

Pete

FDibbins, thanks just seen your post. Will follow instructions and add the attachment.

Thanks

Hopefully this works:

Temp.xlsx

Please also note I used 'Name Manager' to create a white list (which is used in the formula in the Blue, Red and Green Tabs for column D)

Hi FDibbins,

To answer the following questions:
1) The green tab is yet to be populated but it will contain data at a later date. The error that is showing is not really a concern.
2) No, the three colors currently listed are the final amount. No others shall be added nor are there any others present in my master file.
3) The code is the unique identifier. The description is just brought across into the other tabs if a match on the code is found in the White tab.

Please let me know if you need any other details

Kind regards,

Sam

See attached using helper columns (with MATCH) and VLOOKUP

=IFERROR(IF(MATCH($A2,BLUE!$C:$C,0),"B",""),"")



=VLOOKUP($E2&$F2&$G2,$H$1:$I$6,2,0)

Hi FDibbins,

Sorry I didn't mean to sound dismissive with your previous suggestion. I will apply the formula as it definitely seems an improvement.

I have also tried the formula you've suggested and it seems to work perfectly.

Thanks for all your work, it's really appreciated..! This really is one of the best forums I've used and is appears to contain a pool of people with a great amount of talent.

I'll flag this thread as solved.

Kind regards and thanks again,

Sam