VLOOKUP string - references breaking

Rick Girard

I wrote this string with 2 reference sheets. When i first initiated the string all rows returned the required information. After closing the file , I returned to it and found the rows where companies now = NA, the no longer picked up the datapoints in alternative reference sheets and I don't understand why it would work one time then on return show NA... Looking to understand whats happening here.

=IFERROR(VLOOKUP($[Opportunity Num]@row, {SDC_INTAKE SFDC Deal Candidates_022022 Range 1}, 3, false), IFERROR(VLOOKUP($[Opportunity Num]@row, {SDC_Deal Candidates_RDG-SDR Current Opps_0 Range 2}, 3, false), "NA"))

*I also checked each reference sheet, the ranges are correct and had not changed.

There was a 3rd row for SDC_Ceal Candidates that was not In Use and marked 'Processing' - that state never changed and I deleted that reference.

Please advise and thank you.

Rick Girard

Rick Girard

so for each column of the OPPORTUNITY NUM sheet (lets say 10 successive columns need indexing) , each column would contain...

Column1 .... Column 10 and

Return Sheet 1 ... Return Sheet 10.... ?

=IFERROR(INDEX({Column 1 to Return Sheet 1}, MATCH($[Opportunity Num]@row, {Opportunity Number Sheet 1}, 0)), IFERROR(INDEX({Column 10 to Return Sheet 10}, MATCH($[Opportunity Num]@row, {Opportunity Number Sheet 10}, 0)), "NA"))

..and anything between 1 ... 10 will be matched to the appropriate columns ?

Rick Girard

I want to add that I confirmed that the column numbers in the main VLOOKUP sheet match the column #'s in the reference sheets.

Genevieve P.

Hi @Rick Girard

There are a couple of ways we can troubleshoot what may be happening. The first is to take the IFERRORS out of the formulas, then test each VLOOKUP separately to see what error this brings up.

1) Formula errors can create a domino effect, so if even just one of your cells in the 3 column {range} you have identified in either sheet has a formula error in it, this will then cause formulas looking into that column to return the same error. If you test each VLOOKUP without the IFERROR and you see a formula error message that doesn't make sense (eg. Invalid Data Type instead of No Match), then this is likely the cause.

Check your 6 referenced columns and make sure any formulas in those columns have an IFERROR to return a blank cell instead of an error, so this type of domino effect doesn't happen.

2) However if the error is NOMATCH but there actually IS a match in one of the sheets, then we'll need to look at the matching value to see if there has been a change in how this data is being read (eg. if it's being seen as Text instead of Numerical, or vice versa).

3) A final reason for this error could be if the columns in the source sheets were moved around, is that a possibility? If this happened, your {range} would no longer be looking at the correct columns and could return an error.

I would actually recommend using an INDEX(MATCH type of formula instead of each of your VLOOKUPS. INDEX(MATCH evaluates two columns separately: the column with the matching value and the column with the value to bring back. This means that the source sheet can adjust the order and placement of the columns without affecting your formula at all.

An INDEX(MATCH works like this:

=INDEX({Column with value to return}, MATCH([Value to match]@row, {Column to match}, 0))

Let me know if any of this helped!

Cheers,

Genevieve

Rick Girard

Thank you Genevieve for the thoughtful response,, I did sort out the issue, it was triggered by my sorting rows via the filters I have in the reference sheets, whenever i used those filters it broke the range sequence in VLOOKUP when the rows compressed during the filter function (only picking up the viewable rows). So I'm managing around that now.

Regarding INDEX, how would VLOOKUP look like in my formula? Looks like its the reverse, were column VALUE return is first and MATCH column is last . How would that be constructed in my string ?

=INDEX({Column with value to return}, MATCH([Value to match]@row, {Column to match}, 0))

=INDEX({SDC_INTAKE SFDC Deal Candidates_022022 Range 1}, MATCH([@row]), 3, $[Opportunity Num], 0) ??

I would like to see if INDEX could help me regarding the limitation of 100000 cells that can be referenced in a sheet. Would INDEX be more compact than VLOOKUP as I'm running into this limitation now using VLOOKUP across a large sheet (9600 rows) and I have about 20 columns to reference so I'm going over the 100000 cell limit. Any guidance on that?

Genevieve P.

I'm glad you were able to fix the issue.

Yes, INDEX(MATCH would be more compact because instead of referencing 20 columns, you would only reference the cells in 2 columns! It doesn't need any of the middle columns to be selected in the range at all, so your cell number for what's being referenced will decrease significantly.

My apologies, I should have been more clear in the formula structure.

In the MATCH portion of the formula, the [Value to match]@row is the value in your current sheet that you're searching for. In the VLOOKUP version, you have listed at the beginning: $[Opportunity Num]@row

MATCH($[Opportunity Num]@row,

Then the second thing we list is what Column in the other sheet we're looking for that Number in. You would want to create an entire reference for that one specific Opportunity Number column in the source sheet: {Opportunity Number}

MATCH($[Opportunity Num]@row, {Opportunity Number}, 0)

What this does is it finds the row number where this value appears in your source sheet. The INDEX Function then uses this row number, found through the matching value, to return the cell data from a different column.

This means that the first range in your INDEX column is the equivalent to the 3 in your VLOOKUP - what column are you wanting to return? {Column to Return}

=INDEX({Column with value to return}, MATCH($[Opportunity Num]@row, {Opportunity Number}, 0))

Now if you're using the VLOOKUP in multiple columns, swapping out the 3 for 5, etc, this does mean you'll need to create individual Column references for each column:

=INDEX({Column Number 1 to Return}, MATCH($[Opportunity Num]@row, {Opportunity Number}, 0))

=INDEX({Column Number 2 to Return}, MATCH($[Opportunity Num]@row, {Opportunity Number}, 0))

Here's an example of your formula with the VLOOKUPS replaced:

=IFERROR(INDEX({Column 1 to Return Sheet 1}, MATCH($[Opportunity Num]@row, {Opportunity Number Sheet 1}, 0)), IFERROR(INDEX({Column 1 to Return Sheet 2}, MATCH($[Opportunity Num]@row, {Opportunity Number Sheet 2}, 0)), "NA"))

Does this make more sense?

Cheers!

Genevieve

Rick Girard

so for each column of the OPPORTUNITY NUM sheet (lets say 10 successive columns need indexing) , each column would contain...

Column1 .... Column 10 and

Return Sheet 1 ... Return Sheet 10.... ?

=IFERROR(INDEX({Column 1 to Return Sheet 1}, MATCH($[Opportunity Num]@row, {Opportunity Number Sheet 1}, 0)), IFERROR(INDEX({Column 10 to Return Sheet 10}, MATCH($[Opportunity Num]@row, {Opportunity Number Sheet 10}, 0)), "NA"))

..and anything between 1 ... 10 will be matched to the appropriate columns ?

Rick Girard

Thanks i got the index to work, but I couldn't overcome the limitation completely and had to remove columns in my sheet to make room for the highest priority data. This is frustrating, that sheet should allow up to 1,000,000 cells not 100,000 cells, that is miniscule in terms of csv type files.