vlookup and multiple rowns that meet criteria

[Jason]

Hello,

I have a question about a vlookup. I have the below criteria

Supplier Phone Type Prefix Phone number
123 Cell 310 555-1111
123 Office 310 555-1212
123 "Blank" 310 555-1213

In another sheet, I have one supplier and want a column for each phone type.
My below formula won't work because it looks to be only doing a lookup on
the first record (123) it finds. What can I do to have it search for the
correct "Phone type"

=IF(VLOOKUP($B347,'Phone
Number'!$A:$G,5,FALSE)="Office",VLOOKUP($B347,'Phone Number'!$A:$G,6,FALSE)&"
"&VLOOKUP($B347,'Phone Number'!$A:$G,7,FALSE),"")

Any help is appreciated.

Jason

 

[Don Guillett]

Are you saying that you want a LIST of all matches of "Office"
123 Office 310 555-1212
123 Office 310 555-1212
123 Office 310 555-1212
If so, use data>filter>autofilter>filter on type for office>copy/paste.
Record a macro to do it
If desired, send your file to my address below along with this msg and
a clear explanation of what you want and before/after examples.

 

[Jason]

Hi Don,

Not exactly... I have another sheet, that has a lookup for a row with
supplier 123 and column headers for Cell, Office, and "Blank" on the same
row. When I setup the below lookup with text for Cell, Office, "Blank"
respectively, I get a false return value for my "office" lookup because it
finds the first Supplier 123 with "Cell" type. I'd like my lookup to ignore
the first 123 and populate my return value with the "Office" phone number
combo.

Any help is appreciated.

Jason

 

[Don Guillett]

Hard to envision without seeing before/after. As I said,
If desired, send your file to my address below along with this msg and
a clear explanation of what you want and before/after examples.