Help on LOOKUP and INDIRECT formula (#REF being ret'd)

[joka1111]

Hi. I am getting an #REF error when doing my VLOOKUP and INDIRECT formula. I am having it VLOOKUP a month that is a data validation in B5 and using the INDIRECT function to reference the name that is a data validation in B10.

My formula looks like this:

=VLOOKUP(B5,INDIRECT(" ' "&B10& " ' !$A$4:$J$15"),3,FALSE)

Thanks!

[JohnTopley]

This worked OK for me

=VLOOKUP(B5,INDIRECT("'"&B10& "'!$A$4:$J$15"),3,FALSE)

What is in B10 as this is the cause of error Or the extra blanks in your posted formula.

[joka1111]

B10 is a data validation list that has firstname lastname. If I understand the formula, I want it to look for the sheet that matches that name and search within A4:J15 and find the month (B5) and then return column 3 value.

I removed the extra spaces to make my formula look like yours and still get the #REF. Thanks for any help. I am scratching my head trying to get it.

[JohnTopley]

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 Reply then GO ADVANCED and then scroll down to Manage Attachments to open the upload window.


Obviously check B10 exists! (e.g no extra blanks etc in text)

[joka1111]

Desired results are shown in BLUE.

I checked the data validation to ensure no extra spaces before/after and also checked my worksheet names to ensure no extra spaces before/after.

Thanks for any help or guidance you can give.

[Phuocam]

Try:

=VLOOKUP(B3,INDIRECT("'"&B8& "'!A3:J15"),3,0)

[joka1111]

BRILLIANT Phuocam! Thank you SO much!!!