Link to home
Start Free TrialLog in
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..
Avatar of Shums Faruk
Shums Faruk
Flag of India image

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

Avatar of Matt Pinkston
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
Then try below:
=IF(ISNUMBER(SEARCH("NG",A2)),IFERROR(VLOOKUP("NG"&"*",RAPT!B:Q,16,0),""),"")

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

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

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

Open 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?
ASKER CERTIFIED SOLUTION
Avatar of Shums Faruk
Shums Faruk
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.