2 variable lookup but within a table not column

[kevinweinberg]

I'm working on a lookup for wire size and conduit fill and am having trouble. I'm trying to use the index() with the match() formulas. For example:

EXCEL.png

Far left column is wire size. Top row is conduit size. The body is the number of wires that will fit in each conduit size for a given wire size.

I want to provide the wire size (far left column), and number of wires (body of the array), and come up with the conduit size (the top row).

For example, I know I need 5 qty #14 wire, but don't know what size conduit. I need to match a value that is equal or greater 5 in row 2 (where the 14 wire is), and return the value in row 1.

Is that clear?

Thanks for any help in advance!! This is killing me!

[martindwilson]

please attach a work book,

[kevinweinberg]

Book1.xlsx

Thanks!

[ConneXionLost]

Assuming Wire Size is in cell B10, and Number of Wires is in B11, try:

Please Login or Register  to view this content.

Note - Ranges may need to be adjusted since your png does not accurately display row/column numbers/letters.

[ConneXionLost]

Adjusted after seeing the sample sheet:

Please Login or Register  to view this content.

[kevinweinberg]

Adjusted after seeing the sample sheet:

Please Login or Register  to view this content.

That worked perfectly -- can you explain what you're doing here? I'm not quite following. I see you reference column 1 in the array in the beginning, but what's going on with the if(min(index... part?

Thank you so much!

[kevinweinberg]

i've revised the spreadsheet, I pulled a piece out thinking that I could apply it to the whole thing, but I have some wire sizing that's "1/0" and it seems to be messing up the calculation (returns an "N/A"). I've attached a revised spreadsheet. I tried to reference a different sheet like i'm doing in my actual spreadsheet, but somethings messed up.

Thanks again for your help.

Here's the spreadsheet Book1.xlsx

[ConneXionLost]

Hi Kevin,

You had some problems changing the formula. Try this:

Please Login or Register  to view this content.

You also have some data type mismatches that are causing problems. I recommend you force all your wire size references to be text by inserting a single apostrophe in front of the numbers.

[kevinweinberg]

Awesome that worked! Forcing everything to text fixed all my errors (with the above formula) and I was able to transfer it to my master spreadsheet. The only minor change I had to make was the Column() function needed the "$"'s .

Thank you! This is going to save a bunch of time having to look up conduit sizes every time I re-size wire! AWESOME!