PDA

View Full Version : Double lookup



bujaman
03-30-2009, 10:04 PM
Ok,
I have an excel file with multiple worksheets, each with a table with different prices based on measurements. The row and column headings are the measurements. On the last sheet I have a drop-down box that lists the names of the worksheets for the user to select. On that last sheet there is a place where the person using the sheet can enter the measurements they take, and I want the sheet to take the measurements they enter, and the drop-down item they select, and pull the correct value from the correct worksheet. I have been trying to do it with index and match in the worksheet itself, but that gets messy. vlookup doesn't seem to work, either. I have attached what I have so far for your perusal. Any help would be greatly appreciated. Thanks!

Bob Phillips
03-31-2009, 12:18 AM
Try

=INDEX(INDIRECT("'"&$G$4&"'!A1:N15"),MATCH($E5,INDIRECT("'"&$G$4&"'!A1:A15"),0),MATCH($C5,INDIRECT("'"&$G$4&"'!A1:N1"),0))

bujaman
03-31-2009, 08:35 AM
Thanks for the quick reply. That is almost what I need. I put it into my spread sheet, and it worked fine if the values in C5 and E5 exactly matched the width or height in the tables. So, I changed the last argument in the MATCH function to 1, but it rounds the numbers down, not up. I need it to increment to the next highest number, not the next lowest. Is this possible? Thanks again for your help.