VLOOKUP - Multiple lookup Value

sionj55

New Member
Joined
Dec 7, 2020
Messages
1
Office Version
  1. 2016
Platform
  1. Windows
Hello!

Been trying to get my head around this and googling some solutions, but without any luck based on my requirements.

I need to a lookup based on if a cell contains some text. It then needs to lookup a code on another sheet a find a match to the text, and then get an ID. I've got an example template but unsure if I can post on here.

This is my current workings but the issue I have is I have multiple rows with the same Code that I use in the Lookup -

=IF(SEARCH("VEP - ",D3),VLOOKUP(C3,'Lkup Central Budget'!A:E,2,TRUE), " ")

I've added some images if that helps, I may not be explaining clearly enough! A lookup may not be the best thing obviously, but just going off my current knowledge!

1.PNG


2.PNG


Any help will be appreciated!
 

Some videos you may like

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,583
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
I think you can Insert one helper column at first of each table and after input formula hide both:
1. at the first sheet (columns go one to Right Then) :
Excel Formula:
=D2&"_"&E2
or
Excel Formula:
=D2&"_"&LEFT(E2,3)
2. at second sheet (columns go one to Right Then) :
Excel Formula:
=B2&"_"&F2
or
Excel Formula:
=B2&"_"&LEFT(F2,3)

And then Use Vlookup function.
 

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,373
Office Version
  1. 2016
Platform
  1. Windows
Hi SionJ55,

Here's my test Lkup Central Budget

SionJ55.xlsx
ABCDE
1Sericing CodeBudget ItemBudget IDBudget NameBudget Item Name
210-100-1235Id Value 1Budget ID 1Budget 1 20/21VEP - Virtual Engagement Plan
310-100-1234Id Value 2Budget ID 1Budget 1 20/21CDCD - Cat Dog Cat Dog
410-100-1234Id Value 3Budget ID 1Budget 1 20/21EC - Easy Catch
510-100-1234Id Value 4Budget ID 2Budget 2 20/21VEP - Virtual Engagement Plan
610-100-1235Id Value 5Budget ID 2Budget 2 20/21CDCD - Cat Dog Cat Dog
710-100-1235Id Value 6Budget ID 2Budget 2 20/21EC - Easy Catch
Lkup Central Budget


Here's the two criteria match

SionJ55.xlsx
BCDEF
1PONUMBERSericing CodeDescriptionITEMNMBRBudget Item ID
21234567810-100-1234Regular POBudget 1 
31234567810-100-1255Regular POBudget 2 
41234567810-100-1234VEP - Virtual Engagement PlanBudget 1Id Value 4
51234567810-100-1255CDCD - Cat Dog Cat DogBudget 2 
61234567810-100-1234EC - Easy CatchBudget 1 
Sheet2
Cell Formulas
RangeFormula
F2:F6F2=IF(LEFT(D2,5)<>"VEP -","",IFERROR(INDEX('Lkup Central Budget'!$B$2:$B$9999,AGGREGATE(15,6,ROW($C$2:$C$9999)-ROW($C$1)/(('Lkup Central Budget'!$A$2:$A$9999=C2)*('Lkup Central Budget'!$E$2:$E$9999=D2)),1)),"No match"))
 

Watch MrExcel Video

Forum statistics

Threads
1,127,123
Messages
5,622,870
Members
415,935
Latest member
kes1973

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