Avatar of Matt Pinkston
Matt Pinkston
 asked on

MS Excel Multi Sheet Formula

I have a workbook with two tabs

 main & RAPT

 I need a formula for a column in main that will look at column A and see if it has a -NG in it, if it find an -NG then it should use what it found plus 7 so an example would be -NG1595.  Then look in column B of Sheet RAPT and if that -NG#### is found anywhere in that column (could be part of a string) then bring column Q from RAPT across..
Microsoft ExcelMicrosoft Office

Avatar of undefined
Last Comment
Shums Faruk

8/22/2022 - Mon
Shums Faruk

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),""),"")

Open in new window

Matt Pinkston

ASKER
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
Shums Faruk

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

Open in new window

Your help has saved me hundreds of hours of internet surfing.
fblack61
Shums Faruk

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),""),"")

Open in new window

Shums Faruk

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),""),"")

Open in new window

Matt Pinkston

ASKER
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...
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Shums Faruk

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),""),"")

Open in new window

Matt-Partial-Vlookup.xlsx
Matt Pinkston

ASKER
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
Shums Faruk

If something returns 1/0/1900 then you need to format cell as General
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Shums Faruk

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?
ASKER CERTIFIED SOLUTION
Shums Faruk

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Matt Pinkston

ASKER
Awesome!!!!
Shums Faruk

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.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.