VLOOKUP - Multiple lookup Value

[sionj55]

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!

 

[maabadi]

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) :

=D2&"_"&E2

or

=D2&"_"&LEFT(E2,3)

2. at second sheet (columns go one to Right Then) :

=B2&"_"&F2

or

=B2&"_"&LEFT(F2,3)

And then Use Vlookup function.

 

[Toadstool]

Hi SionJ55,

Here's my test Lkup Central Budget

SionJ55.xlsx
	A	B	C	D	E
1	Sericing Code	Budget Item	Budget ID	Budget Name	Budget Item Name
2	10-100-1235	Id Value 1	Budget ID 1	Budget 1 20/21	VEP - Virtual Engagement Plan
3	10-100-1234	Id Value 2	Budget ID 1	Budget 1 20/21	CDCD - Cat Dog Cat Dog
4	10-100-1234	Id Value 3	Budget ID 1	Budget 1 20/21	EC - Easy Catch
5	10-100-1234	Id Value 4	Budget ID 2	Budget 2 20/21	VEP - Virtual Engagement Plan
6	10-100-1235	Id Value 5	Budget ID 2	Budget 2 20/21	CDCD - Cat Dog Cat Dog
7	10-100-1235	Id Value 6	Budget ID 2	Budget 2 20/21	EC - Easy Catch
Lkup Central Budget

Here's the two criteria match

SionJ55.xlsx
	B	C	D	E	F
1	PONUMBER	Sericing Code	Description	ITEMNMBR	Budget Item ID
2	12345678	10-100-1234	Regular PO	Budget 1
3	12345678	10-100-1255	Regular PO	Budget 2
4	12345678	10-100-1234	VEP - Virtual Engagement Plan	Budget 1	Id Value 4
5	12345678	10-100-1255	CDCD - Cat Dog Cat Dog	Budget 2
6	12345678	10-100-1234	EC - Easy Catch	Budget 1
Sheet2

Cell Formulas
Range	Formula
F2:F6	F2	=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"))