Re: Formula not working

Thank you all for your patience with me.
I have discovered a formula which works for me so far
=VLOOKUP(C271,AU19:AX93,MATCH(G271,AU18:AX18,0),0) so this problem has been
diverted.

Thanks


"T. Valko" wrote:

Here's a small sample file:

http://cjoint.com/?hmtvfq5I7N

Notice how the the names in the Type drop down list *match* the column
headers of the table.

--
Biff
Microsoft Excel MVP


"Excluxe" <Excluxe@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:CD5D488A-0503-45BD-BAB7-9A14125B95BF@xxxxxxxxxxxxxxxx
To add on to what I just wrote is it possible to use a hlookup to choose
to
column with the correct pricelist and then vlookup to match the size to
the
price?

"Toppers" wrote:

Create a table as below: as the data appears to be the same for
Spiral..."
and "Steel ..." you only need columns A & B : C is redundant.

As noted by other respondents, the table elements don't match: I've just
copied as you had them.


On Sheet1 (change in formula if not this sheet)

A B C
6x6x4 7.35 7.35
8x8x4 9.05 9.05
10x10x4 12.70 12.70
12x12x4 14.35 14.35
14x14x4 20.50 20.50
16x16x4 32.50 32.50
18x18x4 41.00 41.00
20x20x4 44.50 44.50
24x24x4 50.00 50.00
53.50 53.50
60.00

Then ...

=IF(OR(L74="Spiral Preformed steel galv straight lengths",L74="Steel PVC
coated both sides straight
lengths"),IF(ISNA(VLOOKUP(G47,Sheet1!A:B,2,0)),"No
match",VLOOKUP(G47,Sheet1!A:B,2,0)),"")

IF L74 can only be the above values, the test for L74 is also redundant.

HTH

"Greg Wilson" wrote:

I know. The comment was characterized as being "in addition to what has
already been pointed out" and was "just academic since I agree with
teethless
mama". There's also value in knowing that the formula is structurally
flawed.

I never mentioned it, but I was thinking Match and Index since the
ascending
sort order isn't obvious and may get changed in the future - and
screwed up
in the process.

Regards,
Greg

"T. Valko" wrote:

That formula still won't work properly. As Toppers pointed out, the
lookup_vector *must* be sorted ascending. Place all the size elements
in a
range of cells and then do an ascending sort on them and see how
Excel sorts
them.

--
Biff
Microsoft Excel MVP


"Greg Wilson" <GregWilson@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:4D8F4EC3-1C46-4ECB-B1BD-FE1C928B8DDC@xxxxxxxxxxxxxxxx
So has the problem been solved?

FWIW, I have the formula as structurally flawed in addition to what
has
already been pointed out. The structure of the second (nested) IF
function
is
as follows (with text and array contents abbreviated):
IF(L74="Steel...",LOOKUP(G74,{"6x6x4","8x8x4","10x10x4"}),{7.35,9.05,12.7})
Note how the last array containing numbers is actually the third
argument
of
the IF function and not the third argument of the LOOKUP function.

The following achieves what I assume is your intention. The array
of
numbers
is converted to the third argument of the LOOKUP function and the
third
argument of the IF function is "No result" (change to suit).

=IF(L74="Spiral...",LOOKUP(G74,{"6x6x4","8x8x4","10x10x4","12x12x4","14x14x4","16x16x4","18x18x4","20x20x4","24x24x4"},{7.35,9.05,12.7,14.35,20.5,32.5,41,44.5,50,53.5,60}),IF(L74="Steel...",LOOKUP(G74,{"6x6x4","8x8x4","10x10x4","12x12x4","14x14x4","16x16x4","18x18x4","20x20x4","24x24x4"},{7.35,9.05,12.7,14.35,20.5,32.5,41,44.5,50,53.5}),"No
result"))

However, the above is just academic since I agree with teethless
mama and
think it should be rewritten altogether. Also, as pointed out by T.
Valco,
you have more elements in the numeric arrays than in the size
arrays.

Greg








.