Nested VLOOKUP

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}

sorry, just trying that copying thing...didn't work

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