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,'Pho ne Number'!$A:$G,6,FALSE)&"
"&VLOOKUP($B347,'Phone Number'!$A:$G,7,FALSE),"")

Any help is appreciated.

Jason

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 datafilterautofilterfilter on type for officecopy/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.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Jason" wrote in message
...
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,'Pho ne
Number'!$A:$G,6,FALSE)&"
"&VLOOKUP($B347,'Phone Number'!$A:$G,7,FALSE),"")

Any help is appreciated.

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" wrote:


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 datafilterautofilterfilter on type for officecopy/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.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Jason" wrote in message
...
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,'Pho ne
Number'!$A:$G,6,FALSE)&"
"&VLOOKUP($B347,'Phone Number'!$A:$G,7,FALSE),"")

Any help is appreciated.

Jason

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.


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Jason" wrote in message
...
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" wrote:


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 datafilterautofilterfilter on type for officecopy/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.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Jason" wrote in message
...
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,'Pho ne
Number'!$A:$G,6,FALSE)&"
"&VLOOKUP($B347,'Phone Number'!$A:$G,7,FALSE),"")

Any help is appreciated.

Jason