Three dimensional lookup???

Keep your comb! Try this

=INDEX(Y:AE,MATCH(X1,Y:Y,0)+1+MATCH(J4,$Z$5:$Z$7,0),MATCH(A4,Y3:AE3,0))

Gave me an #N/A Where in there would I insert the named ranges for it to search? Match only allows 1 named range as an option.

I placed that formula in cell L7 of the sample you provided in post #3. It provided the same value that was already there. I then changed the value in cell X1 and it provided the correct result.
Have you changed anything in the file since posting it?

And your sample doesn't have any named ranges.

It does work as long as that specific string is in x1, but when I choose other options for the body/trim it becomes n/a. It's still just pulling from the first table. That number in X1 will change frequently based on customer requests for specific body/trim, etc. The only time it should be N/A is when the customer requests something special. I'm making sure that I am putting in something that I know is in the named range.

Currently, based on the sample you provided, the formula looks for the value found in cell X1 anywhere in column Y. If that value is not found in column Y you get #N/A. If it is found it will retrieve the value from the cell determined by the rest of the formula.

You need to explain where these 'named ranges' are if they're not in columns Y:AE.

Named range F41880b is here ='Series F4'!$Y$3:$AE$7
Named range F41880w is here ='Series F4'!$Y$15:$AE$19
Named range f421480 is here ='Series F4'!$Y$28:$AE$32
Named range f43128b is here ='Series F4'!$Y$9:$AE$13
Named range f43128w is here ='Series F4'!$Y$22:$AE$26

Does that help??

BTW... Thank you SO much for your help. I can maybe see a light at the end of the tunnel now. I may save my hair yet.

So, apart from the sheet name designation, they're in columns Y:AE.

Copy the formula that you are using (directly from the formula bar) and paste it here so we can look at it.

=INDEX(Y:AE,MATCH(X1,Y:Y,0)+1+MATCH(J4,$Z$5:$Z$7,0),MATCH(A4,Y3:AE3,0))

That's what you gave me for L7. For some reason even though I am entering a number that I KNOW is there it's still giving me N/A

Now that the forum seems to be working again - I copied that formula and pasted it into the formula bar (with cell L7 selected) and hit Enter.
I got a value of $134.20. (Because cell X1 was showing F41880B and cell A4 was 2.00 and cell J4 was 0.55)
I then typed F421480 in cell X1 - cell L7 then changed to $3,313.20 (apparently the right result)
I then typed F41880W in cell X1 - cell L7 changed to $147.40 (again, apparently the right result)

Make sure the number that you KNOW is there is exactly the same as what you are inputting in X1 (in other words there are no invisible characters like leading/trailing spaces in the cells in column Y).

Maybe it's because you are hard coding the text. I have a macro set to run because when the required data is entered into the area A4-K4. I have a Concatenate formula based off those choices in cell X3. I have a Macro set with CTRL+T that takes the concatenate set of data and puts it in a useable format for text. Do you think that could be why it isn't working??

I would say so. But it's easy enough to see. Copy one of the numbers from the Y column and paste it into X1 or type one of the numbers directly into X1. If it works (and it certainly should) there is something wrong with the concatenated value.

Yep, that's what it is. Is there anyway to make that work??

This is my concatenate formula =CONCATENATE(B4&D4&E4&C4&G4) because I couldn't use the comma's with text field. Then I have a macro that copies and pastes the value from cell X3 to cell X1.

not text.. i mean numbered cells

I just put =X3 in X1 to test the formula in X3 and it works OK. Why do you need a macro?

And you still have hair!! You're welcome. Don't forget to mark your thread as SOLVED (for instructions see rule #9 by clicking Forum Rules @ top of page)

BTW - your CONCATENATE formula can just be =B4&D4&E4&C4&G4

Originally Posted by Cutter

And you still have hair!! You're welcome. Don't forget to mark your thread as SOLVED (for instructions see rule #9 by clicking Forum Rules @ top of page)

LOL, thank God for small favors. I will go mark it solved. Seriously, you saved me MANY more hours of frustration trying to get this resolved.

Thanks!