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..

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..

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

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

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

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...

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

Matt-Partial-Vlookup.xlsx
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!

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

**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

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?

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

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.

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.

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.

Open in new window