IF statement using length of a cell and VLOOKUP

Sunday_

New Member
Joined
Sep 23, 2022
Messages
1
Office Version
  1. 2021
Platform
  1. Windows
Hello,

I need help fixing the below formula:

=IF(LEN(B11=7)+LEN(B11=5),IF(C13="N/A","N/A",(VLOOKUP(LEFT(B11,SEARCH("S",B11)-1),'Adders calculation'!A:Y,3,0))))

What I need this formula to do is check the length of cell B11, if it's 7 or 5, it should exclude S character and do a VLOOKUP. If the length is different from 7 or 5, it should just do a VLOOKUP. In both cases, if C13 is N/A, result should be N/A.
It works for cell contents with length 5 or 7, but all other lengths it doesn't go to the second if and just gives me #VALUE! as return value.

I appreciate any help I can get.
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
Welcome to the Board!

Right off tghe bat, I see an issue here:
Excel Formula:
=IF(LEN(B11=7)+LEN(B11=5)
The "=7" and "=5" parts should be OUTSIDE of the parentheses.
If you want to just check to see if B11 is 5 or 7 characters long, try this:
Excel Formula:
=IF(OR(LEN(B11)=7,LEN(B11)=5)

And is the "N/A" you are checking cell C13 for literal test "N/A", or the "#N/A" error?
If the "#N/A" error, then instead of this:
Excel Formula:
C13="N/A"
use this:
Excel Formula:
ISNA(C13)
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,192
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top