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

  • 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 Dec 11 '19 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 Dec 11 '19 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 Dec 11 '19 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 Dec 11 '19 at 23:12
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

  • 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 Dec 11 '19 at 23:19
  • Please have a look at the updated formula – marikamitsos Dec 11 '19 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 Dec 11 '19 at 23:55
  • Added the "corrected" form of your mentioned formula. – marikamitsos Dec 12 '19 at 0:14
  • Have you tried the solution offered? Did it work for you? Please check the updated answer and if it solves your question accept it and maybe upvote it so others can benefit as well. If not, let us know. – marikamitsos Dec 17 '19 at 9:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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