use vlookup with offset function

[liem]

I have a drop down menu when I selected a item (merchandise...) I want to
return three row and four columns (B to e)
drop down menu merchandise
Gasoline
Food
A B c d e
merchandise john robert 500 40 4 .2%
merchandise jeff gordon 800 90 4.5%
merchandise julia robel 1000 90 6.5%
gasoline mak dune 50 34 3,2%
gasoline nancy thom 90 37 8.1%
gasoline john good 200 34 5.4%
food john 19 11 4.4%

what is the formula when I used vlookup(merchandise ...)

 

[Teethless mama]

G1: holds drop down menu

=INDEX(E1:E7,MAX((A1:A7=G1)*(ROW(A1:A7))))

ctrl+shift+enter, not just enter

 

[Max]

Just apply and use autofilter's drop list. It'll return exactly what you're
after.

 

[T. Valko]

Try this:

Assume your data is in the range A2:E8

Drop down in cell A11

Enter this formula in cell B11 and copy across to E11:

=IF(ROWS(B$11:B11)<=COUNTIF($A$2:$A$8,$A$11),INDEX(B$2:B$8,MATCH($A$11,$A$2:$A$8,0)+ROWS(B$11:B11)-1),"")

Select the range B11:E11 and copy down to a number of rows that is equal to
the maximum count of any single selection in your drop down list. For
example, "merchandise" appears the most times in your table. It appears 3
times so you have to copy the formulas to at least 3 rows.

Screencap:

http://img107.imageshack.us/img107/6554/lookupuy0.jpg

 

[yshridhar]

Just try this formula
=IF(ROWS($1:1)<=COUNTIF($A$2:$A$16,$G$1),INDEX(B$2:B$16,SMALL(IF(($A$2:$A$16>=$G$1),ROW(B$2:B$16)-MIN(ROW(B$2:B$16))+1),ROWS($1:1))),"")
G1 = merchandise
With regards
Sridhar

 

[Arvi Laanemets]

Hi

It looks like your sourse table will be grouped by items. When this is the
case, then let's assume your source table is on sheet Sheet1, with headers
in row 1, and that the output table will be on some another sheet, with
headers (for name and figure columns) there in row 3 and data validation
list dropdown in cell A1.

1. Define the cell A1 on output sheet sa a named range (Selection in my
example here);
2. For sheet Sheet1 define a dynamic named range (Item as an example)
Item=OFFSET(Sheet1!$A$1,1,,COUNTA(Sheet1!$A:$A)-1,1);
3. Define named ranges
SelectRow1=MATCH(Selection,Item,0)
SelectCnt=COUNTIF(Item,Selection)
SelectRng=OFFSET(Sheet1!$A$1,SelectRow1,,SelectCnt,5);
4. On output sheet, into call A4 enter the formula
=IF(ISERROR(INDEX(SelectRng,ROW()-ROW(A$3),COLUMN()+1)),"",INDEX(SelectRng,ROW()-ROW(A$3),COLUMN()+1))
, and copy it into range A44;
5. Copy the range A44 down for as much rows as you estimate you'll need to
display all rows for any item.

It's done! Select an item in cell A1 on output sheet, and according rows are
displayed. Don't forget - whenever you add data into source sheet, sort the
table by items before processing the report.

 

[liem]

Thank you for all response, but I am sorry I did not made clear on the drop
down from the question last night i asked.
The drop down i have in the worksheet is the comb box macro.
if the user selected "Merchandise" or "Food" then I like the result of
all the merchandise show up in this case from column b2:e3 .
Or if the user selected "Food" on the drop down then the result should be
b7:e7
the data on one worksheet and the comb box in other worksheet.

how can I used lookup function with Offset function in this case?
or a better way to get the result

thanks

liem

 

[T. Valko]

Ok, a combo box makes it a little bit more complicated.

Do you have the combo box linked to a cell? The linked cell will return the
*number* of the selected item. What's the location of the source for the
combo box?

 

[liem]

yes, combo box have link cell b4.
--
thanks

Ok, a combo box makes it a little bit more complicated.

Do you have the combo box linked to a cell? The linked cell will return the
*number* of the selected item. What's the location of the source for the
combo box?

 

[liem]

I tried but how come it began on the second person on the list ( Jeff) and
it skip the first person

 

[liem]

I tried this but on step # 4 after i finished input data it has a blank cell
with no DATA return.
did we identified the data range ?

 

[T. Valko]

Ok, so what is the source for the combo box?

You have to match the value of the linked cell to the source in order to
tell the lookup formula where to look for the data you want. For example:

Suppose you have these selections in your combo box:

merchandise
gasoline
food

If you select food the linked cell returns the value 3 because food is the
3rd item in the list. If you select merchandise the linked cells value is 1
because merchandise is the 1st item in the list. So, you have tell the
lookup formula what the number in the linked cell represents.

 

[liem]

The source of comb box depend on the user what they want to see.
I have merchandise, gasoline for food as the factors in the comb box.
If the user selected "merchandise" in the comb box. how can I get all three
row back at the same time ( B1 to E3) .
john robert 500 40 4 .2%
jeff gordon 800 90 4.5%
julia robel 1000 90 6.5%

or if the user selected Gasoline, i like to have the data back from ( B4: to
E6)

mak dune 50 34 3,2%
nancy thom 90 37 8.1%
John good 200 34 5.4%

or if the user selected Food the result only back on one row b7:e7

john Robert 19 11 4.4%

i hope you can show me the way to get these data back depend on the user
select in the comb box. the comb box point to row a4 and the result show up
in row a5.

 

[T. Valko]

You're not following me in what I'm asking.

See this screencap:

http://img158.imageshack.us/img158/5428/formatcontrolts7.jpg

When you created the combo box you had to enter a source for the items in
the combo box. The source is entered in the box labled Input range
(highlighted in yellow). What did you enter as the Input range? You have to
match the linked cell to an item in the input range. This will tell the
lookup formula what data you want to find.

 

[liem]

I am sorry on the combo box question. my combo box only have source of the
name description data only and i set the range of in the property. such as
Merchandise,Gasoline,merchandise GP,...etc

I re type the formual again I made a mistake of type in your formula and i
tried again. it working as it should.

thanks you very much for your help

liem pho

 

[T. Valko]

Ok, glad you got it working!

 

[Arvi Laanemets]

Hi

I tried this but on step # 4 after i finished input data it has a blank
cell
with no DATA return.

It means INDEX(SelectRng,ROW()-ROW(A$3),COLUMN()+1) did return an error.
Check the range SelectRng - select some item from combo, then go
Insert>Name>Define, select the named range SelectRng and then click on any
part of its source formula: the source sheet is activated with current
SelectRng activated (bordered with dashed line). When you don't see any area
with dashed border, then SelectRng=Nothing, and you have to test all
components of it one by one.

Btw. did you use data validation list in cell A1 on output sheet (i.e. you
did have values like "merchandise"/"gasoline"/"food" there) or was there
combo-box controls selection nunber (1/2/3) instead? When later, then you
have your error cause! I myself use always data validation lists on
worksheet - combo boxes are too cumbersome there.