MS Excel Multi Sheet Formula

Hi Matt,

I am still not sure about plus 7, I am assuming you want see If Col A has -NG then plus 7 Col, which would be Col H, then you want to lookup combination of A & H in RAPT Sheet in Col B to extract Col Q.
If this is the case, then please try below formula.

=IF(LEFT(A2,3)="-NG",IFERROR(VLOOKUP(LEFT(A2,3)&H2,RAPT!B:Q,16,0),""),"")

Select allOpen in new window

so an example of A2 in Main would be

PMNMCI-5000-2015-NG1595 Acronis

and an example of A2 in RAPT could be
NG1595

So we would look for -NG1595 less the (-) in column A of Rapt and when there is a match I want column Q to be grabbed

Then try below:

=IF(ISNUMBER(SEARCH("NG",A2)),IFERROR(VLOOKUP("NG"&"*",RAPT!B:Q,16,0),""),"")

Select allOpen in new window

If NG**** position will be same after PMNMCI-5000-2015- then we can use below formula:

=IF(ISNUMBER(SEARCH("NG",A2)),IFERROR(VLOOKUP(MID(A2,18,6),RAPT!B:Q,16,0),""),"")

Select allOpen in new window

If position is not fixed but NG letters would be 6 digit then try below:

=IF(ISNUMBER(SEARCH("NG",A2)),IFERROR(VLOOKUP(MID(A2,FIND("NG",A2),6),RAPT!B:Q,16,0),""),"")

Select allOpen in new window

HMMMM Results are very weird...

Formula returned a lot of blanks and a bunch of 1/0/1900

Details again...

Two Tabs

Tab1 - MAIN
Column A staring in Row 2 has names that look like this:
PMNMCI-5000-2015-NG1595 Acronis
PMNMCI-5000-2015-NG1424 HW and SW Certification and Accreditation
PMNMCI-5000-2014-NG0225-MOD12 Mobility Consolidation

So need to extract the NG#### and then look in tab RAPT column A which looks like this

NG3498
NG3497
NG3496
NG3495
NG3494

If a match is found the value from column Q should be used...

Matt,

Please find attached with below formula:

=IF(ISNUMBER(SEARCH("NG",A2)),IFERROR(VLOOKUP(MID(A2,FIND("NG",A2),6),RAPT!B:Q,16,0),""),"")

Select allOpen in new window

Matt-Partial-Vlookup.xlsx

Using your formula did not give me the results I was looking for..

MAIN Column(A) = PMNMCI-5000-2015-NG1595 Acronis

RAPT Column(A line 1696) = NG1593

RAPT Column(Q) = 9/30/2015 11:55

But what gets returned is BLANK

If I change the formula to
=IF(ISNUMBER(SEARCH("NG",A2)),IFERROR(VLOOKUP(MID(A2,FIND("NG",A2),6),RAPT!A:Q,16,0),""),"")

I get 1/0/1900

If something returns 1/0/1900 then you need to format cell as General

Matt, see your initial post you asked be  

Then look in column B of Sheet RAPT

What is the expected result, is it a number, currency or date?

Awesome!!!!

You're Welcome Matt, Glad I was able to help.

Is this duplicate question? https://www.experts-exchange.com/questions/29009085/MS-Excel-Formula-Help.html

If yes, then please request it for deletion, else accept the solution there as well.