Returning Non-Zero Vlookup/match/index

**leomiah** · 10-01-2018, 02:20 PM

I have a selection of data (This is an example I'm making from thousands of lines). I'm using code to take the 'Full Value' , if it starts with some select letter combinations "PRO" "BSA" or "PQR" and then pulling the first seven characters. I did this using IF() and LEFT() functions. The problem is many of the 'Full Value' items are under the same 'Case Number.' If this is the case they should have the same 'abbreviated value', even though the 'Full Value' is something random and doesn't start with "PRO" "BSA" or "PQR."

The formula I had for this was:

=IF(LEFT(C2,3)="PRO",LEFT(C2,7),IF(LEFT(C2,3)="BSA",LEFT(C2,7),IF(LEFT(C2,3)="PQR",LEFT(C2,7),VLOOKUP(A2,$A$2:$C$25,2,FALSE))))

The problem with the VLOOKUP section is that is pulls the first value and I can't re-arrange the data so if the first item is "Water" as in this example it returns zero or an incorrect value.

If the Full value does not begin with any of the three listen letters I need it to return the same "abbreviated value" for a matching "Case Number" that is non-zero. Also if there happens to be no matching "Case Number" it will return blank.

Here is an example of some code that is having issues. Column D shows the correct values highlighted in red are the two that are having problems. The formula is in column B. It won't; it won't let me attach the file

Excel.PNG

**63falcondude** · 10-01-2018, 02:41 PM

Try this in B2:

=LEFT(INDEX(C$2:C$23,MATCH(1,(A$2:A$23=A2)*((LEFT(C$2:C$23,3)="PRO")+(LEFT(C$2:C$23,3)="BSA")+(LEFT(C$2:C$23,3)="PQR")),0)),7) Ctrl Shift Enter

**leomiah** · 10-01-2018, 03:25 PM

Originally Posted by 63falcondude

Try this in B2:

=LEFT(INDEX(C$2:C$23,MATCH(1,(A$2:A$23=A2)*((LEFT(C$2:C$23,3)="PRO")+(LEFT(C$2:C$23,3)="BSA")+(LEFT(C$2:C$23,3)="PQR")),0)),7) Ctrl Shift Enter

This worked! Had to modify it but I tested it in the full code and it worked just fine.

Luckily I don't need to do this, but I am curious, since this is a CSE function, how would you get it to return a value besides #N/A for cells that didn't have a return, say for instance with my original example I had some full value "water" and the case number was also random, it wouldn't pull an abbreviated value, could I get that to return something other than #N/A?

Thank you I'll mark this as solved!

**63falcondude** · 10-02-2018, 07:21 AM

could I get that to return something other than #N/A?

Sure. You can wrap it in IFERROR like this:

=IFERROR(LEFT(INDEX(C$2:C$23,MATCH(1,(A$2:A$23=A2)*((LEFT(C$2:C$23,3)="PRO")+(LEFT(C$2:C$23,3)="BSA")+(LEFT(C$2:C$23,3)="PQR")),0)),7),"") Ctrl Shift Enter

This will return blank instead of #N/A.

**leomiah** · 10-02-2018, 08:06 AM

Originally Posted by 63falcondude

This will return blank instead of #N/A.

Could have sworn this didn't work when I tried it yesterday, might not have been hitting CSE, worked this time though! So thank you again.

**63falcondude** · 10-02-2018, 09:02 AM

You're welcome. Happy to help.

Returning Non-Zero Vlookup/match/index

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Returning Non-Zero Vlookup/match/index

Re: Returning Non-Zero Vlookup/match/index

Re: Returning Non-Zero Vlookup/match/index

Re: Returning Non-Zero Vlookup/match/index

Re: Returning Non-Zero Vlookup/match/index

Re: Returning Non-Zero Vlookup/match/index

Thread Information

Users Browsing this Thread

Similar Threads

Returning multiple distinct/repeating values for vlookup or index-match

[SOLVED] Returning Multiple Rows of Data (VLOOKUP/INDEX MATCH)

Index/Match or VLookup returning Multiple Results

Returning second, third etc values using VLOOKUP / INDEX/MATCH

Returning multiple results from vLookup, so reviewing index match instead - understanding

[SOLVED] VLOOKUP (or Index/Match) across four columns -- and returning multiple matches

[SOLVED] INDEX - MATCH - VLOOKUP - returning missing reference

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions