Double lookup from two dimensional table

[bujaman]

Hello all,
I am trying to get a cell in my spreadsheet to look up a value based on two values. I have a dropdown list that lists the worksheets in the workbook, and each worksheet has a table with width measurements for the columns and height measurements for the rows. I have a function that is mostly working, it calls the data from the proper worksheet, but it rounds the measurement values down, and I need it to round to the next highest value on the table. For instance, the measurement may be 55" x 55" in, but the table has values for 54" and 60". The current formula rounds down to the 54" measurement, but I need it to round up to the 60". You can download the file here:

http://telliport.com/excel/

I have highlighted cells and included comments to make it more clear. Thanks for any help, I really appreciate it!

 

[GlennUK]

I can't download files, so can you provide more information on the formulas that you are using and I'll see if it makes sense.

 

[sanrv1f]

it looks like all the intervel is consistant (6) in all the sheets, if it is then you can change your formula to this

=IF(OR(C5=0, $G$4="-"),"",INDEX(INDIRECT("'"&$G$4&"'!A1:N15"),MATCH($E5+5,INDIRECT("'"&$G$4&"'!A1:a15"),1),MATCH($C5+5,INDIRECT("'"&$G$4&"'!A1:N1"),1)))

 

[sanrv1f]

or may be this

=IF(OR(C5=0,$G$4="-"),"",VLOOKUP($C5+5,INDIRECT("'"&G$4&"'!$A$2:$N$15"),MATCH($E5+5,INDIRECT("'"&G$4&"'!$A$1:$N$1"),1),1))

 

[Aladin Akyurek]

it looks like all the intervel is consistant (6) in all the sheets, if it is then you can change your formula to this

=IF(OR(C5=0, $G$4="-"),"",INDEX(INDIRECT("'"&$G$4&"'!A1:N15"),MATCH($E5+5,INDIRECT("'"&$G$4&"'!A1:a15"),1),MATCH($C5+5,INDIRECT("'"&$G$4&"'!A1:N1"),1)))

Does this...

=IF(OR(C5=0, $G$4="-"),"",
    INDEX(INDIRECT("'"&$G$4&"'!A1:N15"),
     MATCH($E5+5,INDIRECT("'"&$G$4&"'!A1:A15"),1),
     MATCH($C5+5,INDIRECT("'"&$G$4&"'!A1:N1"),1)+
      (LOOKUP($C5+5,INDIRECT("'"&$G$4&"'!A1:N1")<>$C5+5)))

yield what you need? If it does, you now need to cope with the fact that C5+5 might exactly equal the value in N1.

 

[sanrv1f]

Hi aladin,

I couldn't understand the last Look up in your code, pls explain

 

[Aladin Akyurek]

Hi aladin,

I couldn't understand the last Look up in your code, pls explain

First things first: Did the formula work as intended?

 

[sanrv1f]

no, its missing a closing parenthesis and returns #N/A if I close it

 

[Aladin Akyurek]

no, its missing a closing parenthesis and returns #N/A if I close it

Possible, with all those INDIRECT's in there... Corrected for missing paren for LOOKUP and also adusted the ranges a bit...

=IF(OR(C5=0, $G$4="-"),"",
   INDEX(INDIRECT("'"&$G$4&"'!B2:N15"),
    MATCH($E5+5,INDIRECT("'"&$G$4&"'!A2:A15"),1),
    MATCH($C5+5,INDIRECT("'"&$G$4&"'!B1:N1"),1)+
     (LOOKUP($C5+5,INDIRECT("'"&$G$4&"'!B1:N1"))<>$C5+5)))

I assume that B1:N1 is sorted in ascending order and the matching to this range needs to be next value in case there is no exact matching value.

 

[sanrv1f]

Hi bujaman

you can use the below formula. if you want to consider the sub-unit columns also in the calculation (column D & E),

=IF(OR(C5=0,$G$4="-"),"",
VLOOKUP($C5+IF(OR(D5="",D5="x"),5,6),
INDIRECT("'"&G$4&"'!$A$2:$N$15"),
MATCH($E5+IF(F5="",5,6),INDIRECT("'"&G$4&"'!$A$1:$N$1"),1),1))

if it is not the case then you can use my earlier suggestion

=IF(OR(C5=0,$G$4="-"),"",
VLOOKUP($C5+5,INDIRECT("'"&G$4&"'!$A$2:$N$15"),
MATCH($E5+5,INDIRECT("'"&G$4&"'!$A$1:$N$1"),1),1))

Hi Aladin,

your suggestion gives me
G5 = 694
when I give
G4 = 2" DS Woods
C5 = 54
E5 = 56

whereas G5 should be 713 for the above input, as the intersection of
Row 6 (houses 54 = Bid!C5 ) and column H (houses 60, next higher value of 56 in Bid!E5) should be the result

I really dont understand the need for the last lookup in your code, without which the formula would yield the desiered result