# VLOOKUP Skip If Value doesn't match



## lifted731 (May 14, 2020)

Hey there, looking for help with a VLOOKUP issue.

I have a google sheet where a row will populate with data adjacent to X if X=10. If Xdoesnot = 10, id like it to look for the next row where X DOES =10.

Current Formula
=VLOOKUP('Raw Data'!A3,'Raw Data'!$A$2:$F$2,2, FALSE)

'Raw Data'!A3 = looking for the value "10"


Hoping this can work! Thanks!


----------



## KRice (May 18, 2020)

Welcome to the MrExcel board!

I don't understand what you are trying to do.  Your formula is saying to search the first column of the array A2:F2 (i.e., it is looking only at A2) for the value in A3.  So if A2=A3, the formula will return the 2nd column of A2:F2 (which is the value in B2).   I doubt this is what you want, but more detail would be needed.  It would be helpful to post a small working example using the XL2BB add-in and providing more information.


----------



## lifted731 (May 18, 2020)

Will do, I'll get a small example from that add-in for you. Thanks!


----------



## lifted731 (May 19, 2020)

So here's my raw data where i pull from for this formula. I have it look for the vehicle ID# here and then populate the row right of it, cell by cell.







_____________________________________________

here is what happens when there is a gap between when #10 appears in the Raw Data. Row 5 is left blank because it did not find the vehicle id #10 in that row. I'm trying to see if there is a way to have it populate without these gaps. essentially skipping raw data until it finds #10 and put that data in the next row regardless of where it is on Raw Data.




Hope that helps, here's a link to the truncated version (omitting excess data) Fleet Maint. Log.xlsx


----------



## KRice (May 19, 2020)

I've had difficulty accessing the file.  I can download it, but Excel won't open it...reports that file is corrupt.  I see two things.  The VLOOKUP formula that you're using shows blank rows (you mentioned this) because the vehicle id in the raw data table doesn't match the target vehicle id.  And the VLOOKUP is reporting the first matching set of data multiple times...I see "oil change" two times, whereas I believe you want "oil change" and "refrigeration repair"?


----------



## KRice (May 19, 2020)

Here is one approach for your #10 worksheet:
Book3ABCDEFGHIJK1Vehicle ID #License Plate #MakeModelYearVINTire SizeRegular DriverACDropbox2107MPE799FordTransit Connect2015NM0LS7E7XF1193974215/55R16 97HAs NeededTRUETRUE3DateMileageMaintenance / Repairs / DiagnosticPerformed ByCost45/6/2020135204Oil Change10 Min Speed Oil Change Center$42.7255/13/2020135204Refrigeration RepairThermoKing$1,712.886     #10Cell FormulasRangeFormulaA2:J2A2='Vehicle Key'!A2A4:A6A4=IFERROR(INDEX('Raw Data'!$B$2:$B$20,SMALL(IF($A$2='Raw Data'!$A$2:$A$20,ROW('Raw Data'!$A$2:$A$20)-1,""),ROWS($A$4:A4))),"")B4:B6B4=IFERROR(INDEX('Raw Data'!$C$2:$C$20,SMALL(IF($A$2='Raw Data'!$A$2:$A$20,ROW('Raw Data'!$A$2:$A$20)-1,""),ROWS($A$4:B4))),"")C4:C6C4=IFERROR(INDEX('Raw Data'!$D$2:$D$20,SMALL(IF($A$2='Raw Data'!$A$2:$A$20,ROW('Raw Data'!$A$2:$A$20)-1,""),ROWS($A$4:C4))),"")I4:I6I4=IFERROR(INDEX('Raw Data'!$E$2:$E$20,SMALL(IF($A$2='Raw Data'!$A$2:$A$20,ROW('Raw Data'!$A$2:$A$20)-1,""),ROWS($A$4:I4))),"")J4:J6J4=IFERROR(INDEX('Raw Data'!$F$2:$F$20,SMALL(IF($A$2='Raw Data'!$A$2:$A$20,ROW('Raw Data'!$A$2:$A$20)-1,""),ROWS($A$4:J4))),"")Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.
The formulas don't play well with merged cells, so I unmerged C:H, entered the formula in C and then centered the result across C:H to achieve the same appearance (I guess this doesn't show when using the XL2BB add-in, but you could do that formatting tweak in your version).  These are array formulas, confirmed after entering by hitting F2, then Ctrl-Shift-Enter (and then you'll see curly brackets appear around the formula).  Drag these formulas down until you no longer see content filling in.


----------



## lifted731 (May 20, 2020)

you are correct sir, it should have been displaying the refrigeration repair.
its working! man i'm so happy! thank you so much!
i'm still learning this stuff and love to know how things work so if you have some time and will to explain how this is achieving the goal i would love to hear it!


----------



## KRice (May 20, 2020)

That's great!  About the explanation...I'm not sure how much of this is already known, so I'll give a fairly complete overview---please bear with me.  All of the formulas that pull information from your 'Raw Data' sheet are nearly identical, differing substantively only in the first argument of the INDEX function.  For example, in cell A4 of the the #10 sheet, we have:
=IFERROR(INDEX('Raw Data'!$B$2:$B$20,SMALL(IF($A$2='Raw Data'!$A$2:$A$20,ROW('Raw Data'!$A$2:$A$20)-1,""),ROWS($A$4:A4))),"")

I'll focus on the middle and end part of the formula:  IF( $A$2='Raw Data'!$A$2:$A$20, ROW('Raw Data'!$A$2:$A$20)-1, "")
The IF function evaluates the 1st argument and if the result is "TRUE", then the 2nd argument is processed by the function, otherwise the 3rd argument is processed.  This IF statement is used to construct an array of list positions where vehicle id #10 (the value in A2) can be found on the range 'Raw Data'!A2:A20 (which lists vehicle ID numbers).  To accomplish this, the function performs an equality test on each element in the 'Raw Data'!$A$2:$A$20 range to determine if it matches the value in A2 on the current #10 worksheet.  The resulting array consists of TRUE's and FALSE's.  You can see this by clicking on the A4 cell, and then in the formula bar, select $A$2='Raw Data'!$A$2:$A$20 and hit F9...Excel will show the array resulting from this series of logical tests.  Be sure to hit Esc after taking this peek, otherwise, that part of the formula can be hard-coded with the array.  The reason this array is generated is because the formula is entered as an array formula, which instructs Excel to execute the formula repeatedly for each item in the range, essentially making a row-by-row comparison.

The TRUE's and FALSE's are then processed by the IF statement: FALSE entries trigger the return of the 3rd argument in the IF statement, which happens to be an empty string ""; while TRUE entries cause the 2nd argument to be delivered, specifically ROW('Raw Data'!$A$2:$A$20)-1
The ROW function returns the row number or numbers corresponding to its argument, so ROW(A2:A20) would return {2;3;4;...;19;20}.  However, since this ROW function is woven into the "TRUE" part of the IF function, the row number of only those cells in 'Raw Data'!$A$2:$A$20 that match the vehicle id # will be incorporated into the final array.  Any FALSE array elements (corresponding to non-matching cells) will be replaced with an empty string "".  Ultimately we do not want the row numbers, but rather the list positions in the data block, and since data begin on row 2, we subtract the number of the row just before the data block (in this case "1").  You might encounter similar constructions such as ROW(C$5:C$20)-ROW(C$4).  This form includes a hint that the row correction is due to the position of data on the sheet and the subtracted term often renders a value that is one less than the row where data begin.  In this example, we end up with the array {1;"";3;"";...;""}, meaning that the 1st and 3rd elements in the 'Raw Data'!$A$2:$A$20 range match vehicle id# 10.  Again the select-F9-view-Esc trick can be used to see this intermediate result, assuming that the entire IF statement, and only the entire IF statement, is selected.

This array is used within the INDEX function to return corresponding data in other columns on the 'Raw Data' sheet.  So how do we control which of the array elements should be processed so that the same list position is not processed twice or missed altogether?  This is the purpose of the SMALL function. To simplify the appearance of the function, I've swapped out the IF function for the result that it gives and inserted some extra spaces:
SMALL( {1;"";3;"";...;""} ,  ROWS($A$4:A4)  ) 
There are two arguments: the 1st is the list of values to select from (the list positions); the 2nd is a number describing whether to return the smallest value in that list (the "1"st smallest), the next smallest (the "2"nd smallest), etc.  We need some way to generate these values of 1,2,...and so on, to ensure that all of the non-empty items in the array are returned one at a time.  In this case, I used ROWS($A$4:A4) to accomplish this.  Note that the first address is fixed $A$4, so think of that as an arbitrary reference point.  As this formula is copied down the sheet, the $A$4 remains fixed (that's what the $ symbols do), but the A4 part of the formula will change to A5 on the next row, A6 below that, etc.  ROWS returns the number of rows in this range, so values from this function will be 1 (A4 to A4), then 2 (A4 to A5), then 3 (A4 to A6), etc.  There is nothing special about the specific cell reference chosen here.  One could achieve the same thing by using ROWS($Z$1:Z1), or ROWS(A$57:$Z57).  These may appear odd, but the important thing is the row designator that begins the range description must be fixed, and the second row designator in that range is the same number, but not fixed.  The actual numbers used and the columns used are completely arbitrary.  Generally it is preferred to use some ranges that apply more directly to the data.  With that said, my formulas would be a little cleaner if I had used ROWS($A$4:$A4) so that the second "A" is fixed, and then when the formula is copied to other columns, the last two-thirds of all formulas would be identical.

With this formula on row 4 in the #10 sheet, the SMALL function extracts the smallest list position index (meaning the value of "1"), and this 1st item happens to be in list position 1, which INDEX uses to return data from other columns corresponding to that list position.  When the formula is pulled down to the next row, the next smallest array element is extracted (meaning a value of "3"), so the 2nd item happens to be in list position 3, which INDEX uses in a similar manner to return data from other columns corresponding to that list position.  Some user intervention is necessary with this construction, as you need to ensure that the formula is pulled down far enough to extract all of the data.  

This approach works well with the INDEX function, since the only part of the formula that needs to be changed is the first argument of INDEX so that data from the desired column on the 'Raw Data' sheet is being extracted.  Finally, it is likely that we'll attempt some things that technically produce errors...for example, trying to return the "3"rd smallest item in the list position array when there are only two non-empty elements.  To prevent the formula from returning an error code, the entire function is wrapped inside an IFERROR function with the instruction to return a "" when an error is encountered.

Please post back if you run into any surprises or have other questions.


----------

