Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

VLOOKUP across multiple sheets

0

I have to VLOOKUP from multiple sheets and I'm using the below formula, but somehow it's working just for some of the cells.

=IFERROR(VLOOKUP($B2,'Retika file 1'!$A$2:$B$3272,2,0),IFERROR(VLOOKUP($B2,'Retika file 2'!$A$2:$B$782,2,0),"Not found"))

Am I missing something?

Answer
Discuss

Answers

0

Hello Roberta and welcome to the Forum.

Your nested (IFERROR(VLOOKUP formula looks okay to me, assuming you want to return a match from the range in sheet Retika file 1 or, failing a match there, a match from a shorther range in sheet Retika file 2 and, in the event of neither having matches, "Not found".

Suggest you check these things:

  1. that the sheet names are correct (and repeated accurately in the formula)
  2. that column A is sorted alphabetically (from A2)- a requirement for a successful match using VLOOKUP.
Discuss

Discussion

The requirement to have a sorted lookup list applies to the MATCH function for some match types but not to VLOOKUP.
Variatus (rep: 4879) Feb 12, '21 at 8:38 pm
@Variatus. Okay but doesn't it also apply for non-exact searches using VLOOKUP? In Excel 2016 at least, the requirement appears in the (fx) function dialog box.
John_Ru (rep: 4312) Feb 13, '21 at 2:30 am
Yes, it does. But Roberta included a 0 in her formula (in place of FALSE) which makes the search an exact one.
Variatus (rep: 4879) Feb 13, '21 at 5:41 am
Ah, yes she did. Thanks for correcting me.
John_Ru (rep: 4312) Feb 13, '21 at 6:51 am
Add to Discussion
0

Look-up values can only be found in the look-up list if they match exactly. We don't know whether you are looking up strings, numbers or dates. Strings tend to have trailing or leading spaces to make them different. Number may actually have more digits than displayed. Dates may be fake or true dates and may be formatted differently. A few days ago I spent half an hour looking for a difference that turned out to be a space added to a string by the cell format.

Since your formula looks OK and works OK the duck rule should be applied which says that if it walks like a duck and quacks like a duck then it probably is a duck. Applied to your case, don't try to blame the formula.

Discuss

Discussion

@Variatus - I too have wasted time on an unnoticed space in a string. You can wrap TRIM( ) around the search argument to clear them there but that doesn't help if the trailing space say is in the searched array.
John_Ru (rep: 4312) Feb 13, '21 at 2:23 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login