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

[seunao]

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

 

[Kuljack]

Maybe try this formula for the first group:

=IF(OR(ISERROR((VLOOKUP(L10,StdardCTRLREF,11,FALSE))),ISERROR(VLOOKUP(L10,LclCTRLREF,11,FALSE)),ISERROR(VLOOKUP(L10,Generic,11,FALSE)))=TRUE,"",CONCATENATE(VLOOKUP(L10,StdardCTRLREF,11,FALSE)," ",VLOOKUP(L10,LclCTRLREF,11,FALSE)," ",VLOOKUP(L10,Generic,11,FALSE)))

and this for the second:

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

 

[seunao]

Thanks Kuljack, unfortunately that did not work, I am unable to upload the document as I don't have permissions to attach documents, I can't upload an image either, so it is pretty difficult to explain

 

[Kuljack]

What happens when you attempt the code? Do you get an error or is it just blank?

You mentioned the codes independently work, not sure why the merged code wouldn't have sufficed.

It's not necessary to upload a document, most won't open it for obvious security concerns.

 

[seunao]

It is blank. Independently they work however not sure why they are blank
When I click on the fx - value_if_false = NA