Using Offset with Vlookup

B

BlackyOakes

Hi

I'm trying to use a vlookup function to reference against the table below

Column ref
A B C D

Jan Feb Mar
London 8 6 1
London 0 2 6
London 1 5 5
London 3 1 9
Paris 8 1 5
Paris 0 8 9
Paris 10 3 0
Moscow 4 8 5
Moscow 9 1 3
Moscow 1 1 5
Moscow 4 4 5
Moscow 0 3 2

Without using row references in the vLookup table array, I want to be able
to find the first Jan value for London (8), and then the second Jan value for
London (0), the third (1) etc down column B. Vlookup obviously finds the
first matching value in Col A only so will only give only the first row of
values in col's B,C, and D (8, 6,and 1). Is there any way to nest an offset
function so that excel can reference values in Col's B,C,D for all rows with
London (also Paris and Moscow) in col A?

Many thanks
 
B

BlackyOakes

Thanks

I actually need to populate another set of tables with the data from the
original table. I am going to use either "London", "Paris", and "Moscow" as
the lookup value and need to have a format that looks like the ones below...

Table 1

Matching vlookup across same row of original table...
Jan Feb Mar
London 8 6 1
Paris 8 1 5
Moscow 4 8 5


Table 2

Matching vlookup and then take values from 1 row down....
Jan Feb Mar
London 0 2 6
Paris 0 8 9
Moscow 9 1 3



Table 3

Matching vlookup and then take values from 2 rows down....
Jan Feb Mar
London 1 5 5
Paris 10 3 0
Moscow 4 4 5



Thanks
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top