HELP!! Using Vlookup

[chrisaustralia]

Hi All. Ok, this is kinda complex, well it is in my eyes anyway.

I have an excel spreadsheet with 2 tabs. What i need 2 do in one tab there is all this raw data. And it goes horizontally. In column J, there is a job number, and this usually goes down 5 rows. In column 0, there are different fields being, typesetting and formatting, revisions, account service, PDFs, Colour Print.
In the other tab, I am trying to put these fields vertical. What I am needing is that when the job number is looked up, it will also reference column 0 and put that figure in. The total figure is in column S in the second Tab. And the figure has to go in column N in the first tab.

Bear in mind that the First row on both tabs is a heading row.

Hope someone can help. Let me know if you require more information.

 

[HalfAce]

With the above screen shots, it seems (if I'm understanding correctly) that if you deleted
or edited the first instance of 'ZU08000' (the one in J2 , Tab 2) your vlookup formula
would return what you're looking for. - or do I misunderstand?

 

[chrisaustralia]

Hi HalfAce...Spot on buddy..Thats exactly how it looks...

 

[HalfAce]

Is deleting/editing the value in Tab 2, J2 an option?

 

[chrisaustralia]

Hi, as once the first formaula is set up, then I will copy that for the next column along to pick up the revision costs..

 

[chrisaustralia]

Bump....Is anyone able to provide some assistance?

 

[chrisaustralia]

Bump...Please!!!

 

[HalfAce]

Hi, as once the first formaula is set up, then I will copy that for the next column along to pick up the revision costs..

Hi Chris,
I'm afraid I don't quite understand what you're saying here.
Are you copying the formula along the columns (to the right) in Tab1 to pickup the value in Tab 2 just below the value for 'typesetting and formatting' then? So on Tab1 you'll have 'ZU08000' in C2 and again in D2, ending up with a horizontal list in tab1 that is looking up values from a verticle list in tab2?

 

[chrisaustralia]

Hi Halface,

The diagram you had was correct. Once the formula is set up like that were its picks up the typesetting and formatting, then I will amend it so that in tab 1 on the next column it will pick up the Revisions cost. But don't worry about that too much. Once the formula is set up for Typsetting and Formatting, then I will be able to make changes to it and use that formaula to pick up the revisions ocst.

 

[HalfAce]

OK, now if I understand this completely, give this a try.
(And this example is directly dependant on everything being in the same locations as in the screen shots I posted earlier.)

Go to Tab 2, highlight the entire range you'll want to refer to in the lookup formulas and give it a defined range name. (Name it LookupTable)

Next, paste this function into a standard module:

Function VLOOKUPNTH(lookup_value, table_array As Range, _
         col_index_num As Integer, nth_value)
Dim nRow As Long
Dim nVal As Integer
Dim bFound As Boolean
  VLOOKUPNTH = "Not Found"
  With table_array
    For nRow = 1 To .Rows.Count
      If .Cells(nRow, 1).Value = lookup_value Then
        nVal = nVal + 1
      End If
      If nVal = nth_value Then
        VLOOKUPNTH = .Cells(nRow, col_index_num).Text
        Exit Function
      End If
    Next nRow
  End With
End Function

Next, in Tab 1, cell N2, enter this formula:
=VLOOKUPNTH($C$2,LookupTable,10,2)
-(returns the value from the 10th column in the lookup table found on the 2nd instance of
the lookup value)

In O2 enter this:
=VLOOKUPNTH($C$2,LookupTable,10,3)
-(returns the value from the 10th column in the lookup table found on the 3rd instance of
the lookup value)

In P2. . .
=VLOOKUPNTH($C$2,LookupTable,10,4)
and so on.

The construction of the formula is:
VLOOKUPNTH(LookupValue, LookupRange, Col_index_num, nth_value)
with the 'nth value' being which instance of the duplicate lookup value you want the formula to return.

Does this help?

 

[chrisaustralia]

Hi Halface,

I kinda understand were you are coming from, but, when I do it it comes up #Name?