Offset a vlookup

**Jymoz** · 02-27-2007, 05:29 AM

is there any way to offset a vlookup formula so that it brings back values other than the first one.

heres the problem:

i have a table with customers and the rentals they have made. in the table it has their customer number and then the item code of the item they have rented out.

i want to have a table that brings up the item codes of what they have rented but with a vlookup it only brings back the first item they have rented and not the ones after that.

**Carim** · 02-27-2007, 05:42 AM

Hi,

Is it an offset feature you are after (i.e. you do input the offset number) or a vlookup() function which would return several possibilities ...?

**Jymoz** · 02-27-2007, 06:25 AM

I think it would be a vlookup as each customer will have several rentals and i will need to pick up on all of them.

It might work as an offset but i dont know how they work so i havent tried

**Carim** · 02-27-2007, 06:31 AM

Why don't you upload a zipped copy of your worksheet ...?

**daddylonglegs** · 02-27-2007, 09:28 AM

Here’s a generic solution.

Supposing you want to lookup a value in C2 within A2:A10 and return all corresponding values in B2:B10 where there is a match.

Use this formula in D2

=IF(COUNTIF(A$2:A$10,C$2)>=ROWS(D$2:D2),INDEX(B$2:B$10,SMALL(IF(A$2:A$10=C$2,ROW(A$2:A$10)-ROW(A$2)+1),ROWS(D$2:D2))),"")

confirmed with CTRL+SHIFT+ENTER

copied down as far as you need. Note: it doesn’t matter if you paste the formula down 10 rows when there are only 2 matches, any unneeded cells will remain blank

Note: to confirm with CTRL+SHIFT+ENTER, paste formula into D2, press F2, hold down CTRL and SHIFT keys and press ENTER at the same time. Curly braces like { and } should appear around the formula in the formula bar and the formula should give the correct result.

**Jymoz** · 02-27-2007, 12:12 PM

with that formula edited to my spreadsheet i do get other values but not ones with that customer number

can someone tell me what i need to do to attach the spreadsheet, i havent got anything that will change it to a zip file

**Teethless mama** · 03-03-2007, 06:02 PM

Originally Posted by Jymoz

is there any way to offset a vlookup formula so that it brings back values other than the first one.

heres the problem:

i have a table with customers and the rentals they have made. in the table it has their customer number and then the item code of the item they have rented out.

i want to have a table that brings up the item codes of what they have rented but with a vlookup it only brings back the first item they have rented and not the ones after that.

Try Auto Filter

Offset a vlookup

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Offset a vlookup

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions