Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!
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..
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..
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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.
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),""),"")
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
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 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),""),"")
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),""),"")
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-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...
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...
Matt,
Please find attached with below formula:
Please find attached with below formula:
=IF(ISNUMBER(SEARCH("NG",A2)),IFERROR(VLOOKUP(MID(A2,FIND("NG",A2),6),RAPT!B:Q,16,0),""),"")
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",A
I get 1/0/1900
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
Matt, see your initial post you asked be
What is the expected result, is it a number, currency or date?
Then look in column B of Sheet RAPT
What is the expected result, is it a number, currency or date?
If you are looking in Col A of RAPT sheet then your formula should be:
=IF(ISNUMBER(SEARCH("NG",A2)),IFERROR(VLOOKUP(MID(A2,FIND("NG",A2),6),RAPT!A:Q,17,0),""),"")
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.
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.
Premium Content
You need an Expert Office subscription to comment.Start Free Trial