1

So, as you can see from the example google sheet below, is there any way to have a formula in which when you try to find the value from both sheets (Example 1, Example 2; if lets say in Example 3, when you try to find the value in Example 1 sheet, it finds nothing and it proceeds to Example 2 sheet to look for the value and if there's any value found, it will return the value to the respective cell)

If =iferror(vlookup(C2,Example1!B:C,2,false)) is empty, try =iferror(vlookup(C3,Example2!B:C,2,false)) to find value in 2 different sheets

Thank you.

Example: https://docs.google.com/spreadsheets/d/1OvyDTfUcrvVUFUptYbmUZ2aYvkq-ON1wfrFyQTM0OCw/edit#gid=1588781085

Improve this question
4
  • Welcome. Please correct me if I am wrong. From your sheet I understand -rephrasing your question - that you could want to have the two values returned in one formula. Would that be sufficient?
    – marikamitsos
    Commented Dec 11, 2019 at 22:14
  • Hi, i managed to solve it and found the formula: =if(iferror(vlookup(C2,Example1!B:C,2,false))="",vlookup(C3,Example2!B:C,2,false)) To return the value if it is found in either one of the 2 sheets. That was what i was asking. But somehow that code works but, for some values in my other sheet it's returning 'FALSE' but not for some others, why?
    – Cheryl
    Commented Dec 11, 2019 at 22:26
  • This returns FALSE in your example sheet as well. Sorry to repeat myself: could you probably just want to have the two values returned in one formula. Would that be sufficient?
    – marikamitsos
    Commented Dec 11, 2019 at 22:37
  • Just 1 value, the formula to find in the first sheet and if it doesn't find anything there, proceed to the second sheet, so in the end we will only have 1 value returned.
    – Cheryl
    Commented Dec 11, 2019 at 23:12

1 Answer 1

Reset to default
0

A workaround would be to use curly brackets {}

For "Bananas" use the following formula 

=QUERY({iferror(vlookup(C2,Example1!B:C,2,false));iferror(vlookup(C2,Example2!B:C,2,false))},"select * where Col1 is not null")

You could also use a "corrected" form of your mentioned formula

=IFS(iferror(vlookup(C2,Example2!B:C,2,false))="",vlookup(C2,Example1!B:C,2,false),iferror(vlookup(C2,Example1!B:C,2,false))="",vlookup(C2,Example2!B:C,2,false))

Please read more about Using arrays in Google Sheets

Have a look at the demo sheet

Improve this answer
7
  • Nono, not using arrays, it returns #REF! anyways. Is there a workaround with my code? =if(iferror(vlookup(C2,Example1!B:C,2,false))="",vlookup(C3,Example2!B:C,2,false))
    – Cheryl
    Commented Dec 11, 2019 at 23:19
  • Please have a look at the updated formula
    – marikamitsos
    Commented Dec 11, 2019 at 23:50
  • "Nono, not using arrays". Why not if it solves your issue? In any case the updated formula does not return any #REF!
    – marikamitsos
    Commented Dec 11, 2019 at 23:55
  • Added the "corrected" form of your mentioned formula.
    – marikamitsos
    Commented Dec 12, 2019 at 0:14
  • I know it is an old question. Still. Have you tried the given solution? Did it work for you? If not we should try finding out why. If yes, as per site guidelines when an answer addresses your question, you should accept it so others can benefit as well. Consider it your contribution to those who help you solve your issues with Google sheets.
    – marikamitsos
    Commented May 26, 2020 at 22:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.