vlookup question

  • Thread starter Thread starter pat67
  • Start date Start date
P

pat67

If the value i am looking for occurs more than once in the array i am
taking it from, how can i show all of the values? Example i am looking
for part 123 in an array an i want to see each cell.

123 production
123 rework
123 delivery
123 quality


the way vlookup works i would only get production

any ideas?
 
If the value i am looking for occurs more than once in the array i am
taking it from, how can i show all of the values? Example i am looking
for part 123 in an array an i want to see each cell.

123     production
123     rework
123     delivery
123     quality

the way vlookup works i would only get production

any ideas?
Click to expand...

Use data>filter>autofiter>filter on the column with 123 for 123
 
Use data>filter>autofiter>filter on the column with 123 for 123
Click to expand...

I am trying to get the information into another sheet. That's why I
was using vlookup. Someone had suggested using offset, but I am unsire
how that works. What i am doing is in one sheet i have a dropdown box
where you can make a selection. From that selection i am looking for
all the data for that selection from the table on another sheet.
 
If the value i am looking for occurs more than once in the array i am
taking it from, how can i show all of the values? Example i am looking
for part 123 in an array an i want to see each cell.

123     production
123     rework
123     delivery
123     quality
Click to expand...

If the data is in columns A and B of Sheet1, one way is to use C as a
helper column.

In C1, put
=B1&IF(COUNTIF(A2:A$1000,A1)=0,"",","&VLOOKUP(A1,A2:C$1000,3,0))

(If there can be more than 1000 rows, replace 1000 by a higher upper
limit.)

Then copy C1 down to row 1000 (or the higher upper limit).

Column C can be hidden.

Now instead if using
=VLOOKUP(123,Sheet1!$A1:$B1000,2,0)
use
=VLOOKUP(123,Sheet1!$A1:$C1000,3,0)

Hope this helps get started.
 
I am trying to get the information into another sheet. That's why I
was using vlookup. Someone had suggested using offset, but I am unsire
how that works. What i am doing is in one sheet i have a dropdown box
where you can make a selection. From that selection i am looking for
all the data for that selection from the table on another sheet.
Click to expand...

If the data is in columns A and B of Sheet1, here’s another way.

In a new sheet, let the drop-down selection be in A1.

In the new sheet, use column B as a helper column. In B2, put
=IF(Sheet1!$A1=$A$1,MAX(B$1:B1)+1,"")
and copy down for the maximum length of the list, plus 1 row. This
picks out the desired rows from Sheet1 and numbers them sequentially.
Column B can be hidden.

In the new sheet, column C will hold the result. In C1, put
=IF(ROW()>COUNT(B:B),"",
OFFSET(Sheet1!$B$1,MATCH(ROW(),B:B,0)-2,0))
and copy down for the maximum length of the list.

Hope this helps.
 
Back
Top