Mapping Formula

[meppwc]

I am stuck with figuring out the correct formula for this situation. Your assistance is greatly appreciated.

Formula involves two worksheets named “MAIN” and “ORACLE”
Formula needed for cell W8327 of MAIN

Lookup value in S8327 of MAIN worksheet in column B of ORACLE worksheet
If found AND column BN of ORACLE equals “INV-MS SERVICE”, then map BH of MAIN to W8327 of MAIN.

If value not found in column B of ORACLE worksheet, then W8327 equals “MSN NOT FOUND”
If value is found in column B of ORACLE and value in BN does not equal “INV-MS SERVICE”, then W8327 equals “NOT ON SERVICE CONTRACT”

 

[meppwc]

Hang on..............think I made a dumb mistake..............be right back

 

[KRice]

I’m not at a computer at the moment, but I’d like you to look at two things:
1. In a recent post, you mentioned Oracle while the formula uses upper case ORACLE. Check your worksheet name and match the same case.
2. If there is no issue with the text case, then the formula is not seeing the initial match. So there is some problem there that needs to be investigated. You mentioned Oracle B5 having contents of 3100R300888, I’m assuming without quotes. In some empty cell on your MAIN sheet, enter

=Oracle!B5=3100R300888

…and let me know if it returns a result of TRUE or FALSE.

 

[meppwc]

I think the problem is right here in the formula: ",$BH8327"
If the value in MAIN S8327 is found in ORACLE column B, then map back the value in column BH in ORACLE on the same row where the value was found.
So, the value in the lookup was found on Oracle cell B5, so map back the value found in Oracle cell BH, which is 5048101
If I try to change ",$BH8327" to ,ORACLE!BH:BH, it returns #SPILL!

 

[meppwc]

Also, to answer your questions, both worksheets MAIN and ORACLE, are uppercase
And the lookup and results values do not have quotes
If you look at my above post, I believe the change needed is in this spot...................,$BH8327

 

[KRice]

Your first post said:

If found AND column BN of ORACLE equals “INV-MS SERVICE”, then map BH of MAIN to W8327 of MAIN.

"BH of MAIN"...are you now saying you want BH of ORACLE instead? If so, that would be:

=LET(r,MATCH($S8327,ORACLE!$B:$B,0), IF(ISERROR(r),"MSN NOT FOUND",IF(INDEX(ORACLE!$BN:$BN,r)="INV-MS SERVICE",INDEX(ORACLE!$BH:$BH,r),"NOT ON SERVICE CONTRACT")))

 

[meppwc]

WooHoo............that did it. Now all I have to do is apply this formula for the entire column and test to see if I get the desired results..............thanks so much for you assistance and patience.

 

[KRice]

That's good to hear...happy to help.