MS Excel Multi Sheet Formula
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..
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),""),"")
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
=IF(ISNUMBER(SEARCH("NG",A2)),IFERROR(VLOOKUP(MID(A2,18,6),RAPT!B:Q,16,0),""),"")
=IF(ISNUMBER(SEARCH("NG",A2)),IFERROR(VLOOKUP(MID(A2,FIND("NG",A2),6),RAPT!B:Q,16,0),""),"")
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-MO
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...
Please find attached with below formula:
=IF(ISNUMBER(SEARCH("NG",A2)),IFERROR(VLOOKUP(MID(A2,FIND("NG",A2),6),RAPT!B:Q,16,0),""),"")
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",A
I get 1/0/1900
Then look in column B of Sheet RAPT
What is the expected result, is it a number, currency or date?
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.
Gain unlimited access to on-demand training courses with an Experts Exchange subscription.
Get AccessWhy Experts Exchange?
Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.
When asked, what has been your best career decision?
Deciding to stick with EE.
Being involved with EE helped me to grow personally and professionally.
Did You Know?
We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE
Ask ANY Question
Connect with Certified Experts to gain insight and support on specific technology challenges including:
- Troubleshooting
- Research
- Professional Opinions