How to ignore prefixes with Vlookup

cafabrofon

New Member
Joined
Feb 8, 2021
Messages
4
Office Version
  1. 2019
Platform
  1. Windows
Hello,

I have a formula in mind =IFERROR(VLOOKUP(A17,Sheet2!A:B,2, 0), "") the value I want to look for usually contains two letters and five numbers like FV12345 but the letters change depending on my tool set so I want to ignore the F% from the reference so that when I type the number no matter what the prefix is, it returns my tool time on the other column. so Sheet2!A contains the F%12345 and I want it to return column B. can anyone help?
 

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

cafabrofon

New Member
Joined
Feb 8, 2021
Messages
4
Office Version
  1. 2019
Platform
  1. Windows
I am not sure where in the formula to plug the RIGHT(A1,5
 

AhoyNC

Well-known Member
Joined
Oct 10, 2011
Messages
4,749
Office Version
  1. 365
Platform
  1. Windows
Try:
IFERROR(VLOOKUP("*"&A17,Sheet2!A:B,2, 0), "")

It is best not to reference whole columns when using the IFERROR function as this function will look at all 1 million plus rows in the worksheet which can slow down calculations.
 

cafabrofon

New Member
Joined
Feb 8, 2021
Messages
4
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

Try:
IFERROR(VLOOKUP("*"&A17,Sheet2!A:B,2, 0), "")

It is best not to reference whole columns when using the IFERROR function as this function will look at all 1 million plus rows in the worksheet which can slow down calculations.
that did not work. it returns what I want but only with specific prefix. its not ignoring the prefix
 

JamesCanale

Board Regular
Joined
Jan 13, 2021
Messages
119
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
How would something like this do:
MrExcelPlayground.xlsx
ABCDE
2NamelookupTable with prefixValue
3Harry7kljasdf Jamie5
4Jamie5sadjklf Fred6
5Fred6asdjlkfa Harry7
6John8asldkfj John8
Sheet13
Cell Formulas
RangeFormula
B3:B6B3=INDEX($E$3:$E$6,MATCH(1,SIGN(SEARCH(A3,$D$3:$D$6))))

This isn't doing great when it finds no match for reasons I can't figure yet.
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,343
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

It's not that clear what you are entering in your lookup cell, but maybe either of these
+Fluff 1.xlsm
ABCDE
1
212347CFV12345A
3FV12349EFV12346B
4FV12347C
5FV12348D
6XY12349E
7FV12350F
Main
Cell Formulas
RangeFormula
B2B2=IFNA(VLOOKUP("*"&A2,D2:E7,2,0),"")
B3B3=IFNA(VLOOKUP("*"&RIGHT(A3,5),D2:E7,2,0),"")


Failing that, can you post some sample data.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.
 

Saba Sabaratnam

Board Regular
Joined
May 26, 2018
Messages
196
This will be your formula if you use RIGHT formula

IFERROR(VLOOKUP(RIGHT(A17,5),Sheet2!A:B,2, 0), "")

Kind regards

Saba
 

cafabrofon

New Member
Joined
Feb 8, 2021
Messages
4
Office Version
  1. 2019
Platform
  1. Windows
Thanks guys, I have the formula working like this =IFERROR(VLOOKUP("*"&RIGHT(C14,5),Sheet6!$A$1:$B$50,2,0), ""). It is finally ignoring my prefixes because of the RIGHT function. now, I can't get it to stop returning something for empty cells. what am I missing?
 

Attachments

  • Capture.PNG
    Capture.PNG
    20.5 KB · Views: 2

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,343
Office Version
  1. 365
Platform
  1. Windows
How about
Excel Formula:
=IF(C14="","",IFNA(VLOOKUP("*"&RIGHT(C14,5),Sheet6!$A$1:$B$50,2,0), ""))
 

Watch MrExcel Video

Forum statistics

Threads
1,127,156
Messages
5,623,079
Members
415,951
Latest member
Shen1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top