Back to Forms in Excel VBA archive index

Back to archive home

Hello:

Any help on this topic would be very appreciated. I have to create a function that would first look to one variable, then to another and return an appropriate value. I cannot use nested if-then statements because I have ten (10) variables for the first item and about 100 for the second. The first variable is an item that is called S1 - S10. The second variable is a run rate for each S*. Thus, I need a formula that will first pull the correct code, then look for the appropriate run rate, and return the correct price for the run rate.

My formula at the moment looks like:

=IF(B117=0,0,(D117*(C117*(VLOOKUP(B117,'Press Run'!A1:D100,(VLOOKUP((D117*1000),'Press Run'!A1:D100,4)))))))

In changing certain items, I either have a "#N/A" returned or "#REF."

Thanks in advance for any help you can give.

Check out our Excel Resources | ||||

Mike,

I see you're using the same table for both VLOOKUPs. I've some idea of what you're trying to do. However, if you could post 10 rows from the range A1:D100, a direct answer instead of a guess would be possible.

If you care to post your data, activate an empty cell, type =, select first 10 rows (including column headings), hit control+shift+enter at the same time, go to the formula bar while this cell is active, hit F9, and copy what you see between brackets then paste it in the follow up.

Aladin

Aladin:

Thanks for the quick response. Below are the rows for the first two codes. I had a little trouble going about it your way, so I copied and pasted it and formatted it a litte. The formula will need to look to an independent cell (B117) to get the press code. I then need the formula to go to this table, go to the correct area on the table for that code, and compare the run rate to the run rate in another indendent cell (D117*C117). The lookup will then need to pull the appropriate Cost/M for that run rate and code.

Any help you can give is very appreciated.

Code Run Rate Impressions Cost / M

S1 6,000 - 21.40

S1 6,500 999 19.75

S1 7,000 1,999 18.34

S1 7,500 2,999 17.12

S1 8,000 3,999 16.05

S1 8,500 4,999 15.10

S1 9,000 6,999 14.27

S1 9,500 8,999 13.51

S1 10,000 10,999 12.84

S1 10,250 12,999 12.53

S1 10,500 14,999 12.23

S1 10,750 17,499 11.94

S1 11,000 19,999 11.67

S2 6,000 - 24.25

S2 6,500 999 22.38

S2 7,000 1,999 20.78

S2 7,500 2,999 19.40

S2 8,000 3,999 18.18

S2 8,500 4,999 17.11

S2 9,000 6,999 16.16

S2 9,500 8,999 15.31

S2 10,000 10,999 14.55

S2 10,250 12,999 14.19

S2 10,500 14,999 13.85

S2 10,750 17,499 13.53

S2 11,000 19,999 13.22

Mike

Aladin:

The formatting did not work well. The codes are S1 or S2. The Run Rate are even numbers (5250, 5500, etc.). The impressions are 0 or end in 999. The next number is the cost/m. The impressions number is not necessary to the formula - I am using it for another purpose and it can be dropped from the table if needs be.

Mike

{"DRAWING #","TYPE","NAME","REV #","DATE";"1014W42","BT","BOLT, EYE, CLOSED, SS",0,25427;"1014W6","BT","BOLT, EYE, SHLDR PATTERN, CARB STEEL, HEAT TRTD","D",23301;"121W112","OTH","FELT SEAL","C",26795}

Mike,

Ok, here we go.

I assume that

(1) you want to do lookup in a different worksheet than the Press Run sheet;

(2) the data on Press Run is sorted on column that has S-values. I'd advise to use a macro to do sorting & one that fires up whenever something changes with(in) your data (if you don't how, you might want to start another thread in which you sollicite for help on this matter.

Change the sheet name "Press Run" to "PressRun".

On PressRun, list all your s-values (s1 to s10) in some column, say in F from F2 on.

In G2 enter: =COUNTIF(A:A,F2)+G1+1 [ copy down as far as needed ]

Select the range F2:G1 and name the selected range SROWS via the Name Box (see the Formula Bar; just type SROWS and hit ENTER).

On the sheet where the lookup will take place, we have

B117 that contains the press code of interest.

The lookup value is C117*D117.

Use the following formula to get the Cost/M value associated with the lookup value if the lookup value exists or #N/A if not.

=VLOOKUP(C117*D117,INDIRECT("PressRun!"&ADDRESS(MATCH(B117,PressRun!A:A,0),2)&":"&ADDRESS(VLOOKUP(B117,SROWS,2,0),4)),3,0)

Legend:

2 --> column B

4 --> column D

and

3 is the column index for Cost/M.

Hope I understood you the way you intended.

Aladin

Aladin:

Thanks for the help. I'm going to give it a try. I've been spinning my wheels with this for a while now.

Mike

This archive is from the original message board at www.MrExcel.com.

All contents © 1998-2004 MrExcel.com.

Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.

Microsoft Excel is a registered trademark of the Microsoft Corporation.

MrExcel is a registered trademark of Tickling Keys, Inc.

All contents © 1998-2004 MrExcel.com.

Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.

Microsoft Excel is a registered trademark of the Microsoft Corporation.

MrExcel is a registered trademark of Tickling Keys, Inc.