VLOOKUP with INDIRECT and SUBSTITUTE

Hi Guys.

following on from my previous post, I think I'm nearly there, but can't get this to work:

Please Login or Register  to view this content.

I need it to do the following:
IF B13 is blank, then be blank, Otherwise search for B13 cell content in a table name specified in cell C4. I need it to substitute the spaces in the result of C4 eg, it could be that the user selects 'Caledonia In Frame' from C4's DV list, and I need the VLOOKUP to search table 'CaledoniaInFrame'..

You probably should have continued in the original thread!

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Very nearly, both of you. Wher was right: there is a bracket missing from the INDIRECT function, but one of the 3 at the end needs to go...

=IF(B13="","",VLOOKUP(B13,INDIRECT(SUBSTITUTE($C$4," ","")),2,FALSE))

Originally Posted by Glenn Kennedy

Very nearly, both of you. Wher was right: there is a bracket missing from the INDIRECT function, but one of the 3 at the end needs to go...

=IF(B13="","",VLOOKUP(B13,INDIRECT(SUBSTITUTE($C$4," ","")),2,FALSE))

Perfect, Thanks - you are wonderful!!

One more.. A bit further nested, and again I can't get it right..

Where B13 is found as above, now I need M13 to return the price for the same search, which is found by matching C3 to the table specified in C4's row headers. So it needs to search for B13 and return a price from column matching C3:
C4 Specifies which table to search
C3 specifies the PG01 or PG02 etc for the quote..
Table has headers; PG01, PG02, PG03 etc, with different prices under each
Table Row A Contains my product codes being searched in B13
Table Row B Contains product description (Being returned in cell For the code you helped with before)
Table Rows D to S contain the PG (Price group) headers, with pricing under for each price group.

I keep coming up with #Ref! errors no matter how I try it!..

Nope. I have tried to mock this up; but I'm confused. I need to see your sheet.
Please attach a sample workbook (preferably also showing the 1st formula in place and working). Make sure there is enough data to demonstrate your need. Make sure your desired results are shown, mock them up manually if necessary. Remember to remove ALL confidential information first!!!

However, nothing on this Forum works quite as well as you might expect. The attachment icon doesn't work any more!! (Helpful, isn't it!!). Instead, just before you submit your post, click on GO ADVANCED (near the bottom) and then scroll down to Manage Attachments to open the upload window. The relevant instructions are at the top of that screen.

Thanks so much for looking at this for me.
Okay, I think it's attached now. Please see the boxes with the Red border, and the individual cell notes that have been added. In particular, I'm trying to get the right formula for cell M13 here. Your previous formula is in cell C13.

Thanks again!

In C3, you have an extra space that's mucking things up:

=IF(C5="Select...","Select Range & Door Style",TRIM("PG"&LEFT(C5,2)))

In M13, use this:

Formula:

Please Login or Register  to view this content.

Originally Posted by Glenn Kennedy

In C3, you have an extra space that's mucking things up:

=IF(C5="Select...","Select Range & Door Style",TRIM("PG"&LEFT(C5,2)))

In M13, use this:

Formula:

Please Login or Register  to view this content.

Hmm.. That works perfectly when I implement it on my own test sheet, but not on my main sheet.. something is amiss..

Originally Posted by RichardJSigKits

Hmm.. That works perfectly when I implement it on my own test sheet, but not on my main sheet.. something is amiss..

Sorted! On my main doc, my quote sheet is named differently!

Thank you so much for your help - you are extremely kind!

Are calculation options set to manual?

Edit: I see you found the problem!!

Originally Posted by Glenn Kennedy

Are calculation options set to manual?

Edit: I see you found the problem!!

All sorted - Cheers!