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

**starguy** · 02-27-2007, 02:19 PM

Originally Posted by Jymoz

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

if you use winXP, right click on the file and Send to compressed zipped folder then attach it.

**daddylonglegs** · 02-27-2007, 04:06 PM

Originally Posted by Jymoz

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

What formula are you using and in what cell are you starting?

**Jymoz** · 03-01-2007, 05:46 AM

Originally Posted by daddylonglegs

What formula are you using and in what cell are you starting?

I'm using the =IF(COUNTIF..... formula you gave me

the number i am looking for is in cell B28 and i am searching for it on another sheet cells B24:C55

In that table the customer number (what i am searching for) is in column B and the item code (what i want to return) is in column C.

i'll attach the spreadsheet later as i am unable to do it at the moment

**Carim** · 03-01-2007, 05:59 AM

Hi,

Could this previous post help you out ...?
http://www.excelforum.com/showpost.p...98&postcount=5

**Jymoz** · 03-01-2007, 02:04 PM

ok here is the part of my system that is needed for this.

as you can see i have the table on one page with the customer numbers and the item codes of the items they have rented. On the other page is the lookup where i can only get the first item they have rented

**Carim** · 03-01-2007, 02:20 PM

Hi,

See attached ...

**Jymoz** · 03-01-2007, 05:47 PM

hi

i've finally got it, thanks for all your help on this i really do appreciate it

**Jymoz** · 03-02-2007, 05:38 AM

although when trying to put the formula into my sheet there is problem - in the example to gave me there is a { and } at the beginning and end of the formula, which disappear when i change it. It doesnt seem to want to work without these.

what are they for and what can i do to fix it?

**Carim** · 03-02-2007, 05:58 AM

To input an array function, use Control Shift Enter instead of Enter ...

**Jymoz** · 03-02-2007, 11:12 AM

ahh i see, thanks

**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

Offset a vlookup

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions