# VLOOKUP - Multiple lookup Value

#### sionj55

##### New Member
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!

Any help will be appreciated!

### 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"

##### Well-known Member
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.

##### Well-known Member
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"))

Replies
3
Views
225
Replies
3
Views
316
Replies
5
Views
177
Replies
17
Views
175
Replies
4
Views
154

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.

### Which adblocker are you using?

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

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