How do I use VLookup with an OR/AND statement or Nested IFs

Hello,

I have an issue I can't seem to figure out

Question: How do I combine multiple VLookups in 3 datasets into into one formula. I will like to be able to select an option in a given drop down list, it will compare different worksheets and populate data relevant to what has been selected:

Individually the below formulas work and pull the relevant data from the relevant cell, but I need to be able to put them all in one cell (one formula).

Once Group_1 set of formula are run the relevant data in Group_2 is pulled into the right column.

Group_1
=IF(ISERROR(VLOOKUP(L10,StdardCTRLREF,11,FALSE)),"",VLOOKUP(L10,StdardCTRLREF,11,FALSE))
=IF(ISERROR(VLOOKUP(L10,LclCTRLREF,11,FALSE)),"",VLOOKUP(L10,LclCTRLREF,11,FALSE))
=IF(ISERROR(VLOOKUP(L10,Generic,11,FALSE)),"",VLOOKUP(L10,Generic,11,FALSE))


Group_2
=IF($M10="Key",VLOOKUP($L10,StdardCTRLREF,5,FALSE),0)
=IF($M10="Key",VLOOKUP($L10,LclCTRLREF,5,FALSE),0)
=IF($M10="Key",VLOOKUP($L10,Generic,5,FALSE),0)


Any suggestion to achieve this is most welcome.

Thank you

Thank you for your response Glenn, Please see requested info attached:

Worksheet related information

My issue is focused in the R2R worksheet What I am trying to achieve is as below:

• When I select an option from column L10 onwards dropdown list:
• This will update the related Key control(Column M) from the necessary database
• This will also update columns E-J

However I am able to only get this to work presently if the formula is to only one specific dataset/database not all 3

I will like to be able to select the data from the dropdown list in Column L (row 10 onwards) which will then populate then populate the respective columns E-J and M based on the appropriate database.

Data in the dropdown list in column L are outlined in the datasets
StandardControlRef - Standaards database
LocalControlRef - Local Databse
Generic - Generic Database

Please let me know if you need any additional information.

Thank you

The workbook contains quite a few other sheets which I deleted before sharing, I have however shared additional sheets as they are identical to the R2R worksheet. Those inoperative named ranges come into play in those sheets.

I have attached an image explaining the below

Yes if you select C-FM2740(Standard control database) in cell L10; M10 returns the corresponding value in this case, 'Key', while E10 - J10 return respective values from their respective name range in this case StandardControlRef

if you select C-FM2785(LocalControl database) in cell L11; M11 returns the corresponding value in this case, 'Supporting', while E10 - J10 return values from LocalControlRef

if you select C-FM2720(Generic control database) in cell L12; M12 returns the corresponding value in this case, 'Key', while E - J return values from GenericControlRef

There won't be an option for Null except where 'spare row' is selected

Column L - pulls it's data from R2R (A108 Onwards)

With regards to duplicate values, I will be working on a formula to ensure there are no duplicate values in the control name column of the 3 database sheets, Also column L will only pull data from the R2R sheet A108 onwards..

A108 onwards is however completed by selecting data from respective name ranges in the database sheets.

I managed to get this formula to pull data but there is still an error in it:
=IF(ISERROR(VLOOKUP(M10,StandardCONTROLREF,12,FALSE)),IF(ISERROR(VLOOKUP(M10,GenericCONTROLREF,12,FALSE)),IF(ISERROR(VLOOKUP(M10,LocalCONTROLREF,12,FALSE)),"",VLOOKUP(M10,LocalCONTROLREF,12,FALSE)),VLOOKUP(M10,GenericCONTROLREF,12,FALSE)),VLOOKUP(M10,StandardCONTROLREF,12,FALSE))


Please let me know if you have any further questions

Hi Glenn,

Sorry I create my profile back in 2009 when I was working with Office 2003 and I haven't updated it since. I now use 2010 hence the xlsx file, I will update the profile to reflect this. I apologise if that was misleading.

Yes thank you!!! .
I believe the workbook with the title "one way" is a cleaner approach.

I was trying to adapt it for cells E10 - J10, but found a flaw in my formula which is, if the value is not "Key", it fails to populate the table, but the value can be key or supporting (which is pulled from R2R K108 onwards) and it should populate the cells with the respective data from R2R rows A108 onwards

=IF($M10="Key",VLOOKUP($L10,StandardCONTROLREF,5,FALSE),0)

I should be using the Indirect here so the data is read from the 3 datasets and not just the specified dataset.

Can you please assist with this?

Thank you Glenn, this worked excellently.