VLOOKUP+OFFSET outside named range

Topic

New Reply

Hi,

Got two sheets ‘1’ & ‘2’. ‘1’ is presentation, ‘2’ extracts from various sources.

Got a named range ‘LookupHere’ in ‘2’ – say [C3-E20]. Column B has values from ‘elsewhere’ (B3:B20).

In sheet ‘1’ Cell A2 – I got a value I would like to lookup in sheet ‘2’ in Col C, when found offset by 0,-1 and get the value from ‘B’.

How do I offset outside my named range??

=vlookup(FindThis,LookupHere,OFFSET(1,0,-1),FALSE) does not work

PS: I’m not so familiar with eg. MATCH and INDEX – maybe they would do a better job in this case??

Reply | Quote

Replies

Hi,

=INDEX(2!$B$2:$B$20,MATCH(A1,2!$C$2:$C$20,0))

Should do the trick

Reply | Quote

Hi,

=INDEX(2!$B$2:$B$20,MATCH(A1,2!$C$2:$C$20,0))

Should do the trick

Not quite – I get a #NAME error.

Does the value I’m looking for have to be unique??
In the range c2-c20 there are multiple occurences of the value…
– so I’m planning to build the formula into a VBA loop sequence.

Case is:
sheet 1
– got a list of person names,

sheet 2
– got a large range, all sorts of values,
– 1 row per company/client,
– col B contains ‘Company/Client name’
– col C is person name

On sheet 1 – I must create a list
Looping through the list of ‘Person names’.
(First row is ‘Person name’ – underneath I show each of his companies/clients)
End loop

Is it more clear what I try to achieve this way…?

Reply | Quote

Not quite – I get a #NAME error.

To get this to work
=INDEX(2!$B$2:$B$20,MATCH(A1,2!$C$2:$C$20,0))

rename your sheet 2 to ‘2’ (without quotes)

Reply | Quote

Please attach a workbook with some sample data and an example of the desired output

Reply | Quote

Tried to make a sample workbook to the best of my humble ability and modified the formula as ‘Gfamily’ suggested – still no luck…

Reply | Quote

There are three problems:

[*]The names of the sales persons are “SalesPerson 1” etc. on Sheet 1, but “SalesPerson1” etc. on Sheet2, so you’ll never get a match.
[*]The lookup range LookupHere should be a single column – it should refer to Sheet2!$E$9:$E$40
[*]MATCH and INDEX will return only the first instance found.

If you correct 1. and 2., the first formula will work, but the others will return the same value.

I’d suggest using Microsoft Access -you’re basically trying to build queries on several tables, so Access is much more suitable for this than Excel.

Reply | Quote

Thx Hans

#1 – is a typo on my side, when making the example. It’s not a problem in the real file.

#3 – data originates from multiple extracts on various databases, so re-entering sounds a bit redundant to me

#2 – hmmm, gotta think of another approach to this then. This specific problem is just a fragment of a larger set. Having one named column per item is a ‘no go’. It will require to much maintenance over time.

To Jan, Gfamily and Hans – I appreciate your efforts and inputs on this – Thanks.

Reply | Quote