Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 275
Default VLOOKUP puzzle ??

Hi all,
getting a little frustrated with what I though would be a simple VLOOKUP
formula, so turn to you for help.

Cells C6:G80 are populated with data
I want the whole of column G to be searched for the exact code FQR and when
found return the data in the corresponding columns E and D

=VLOOKUP("FQR",D6:G80,2,TRUE)&" "&VLOOKUP("FQR",D6:G80,1,TRUE)

the above gives the wrong answer, and if I use the FALSE array, ie to find
exact match, I get the #N/A error,
Can anybody provide the correct formula

Thanks
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,574
Default VLOOKUP puzzle ??

In order for FALSE to work your data needs to be sorted in ascending order by
the column you're looking up.

Since you're concatenating two lookups on two separate columns, that is
impossible.

I would suggest that you concatenate columns E & D prior to running the
VLOOKUP, and then have the VLOOKUP lookup in the concatenated column. So,
for example: =VLOOKUP("FQR",D6:H80,2,FALSE)

Dave
--
Brevity is the soul of wit.


"Anthony" wrote:

Hi all,
getting a little frustrated with what I though would be a simple VLOOKUP
formula, so turn to you for help.

Cells C6:G80 are populated with data
I want the whole of column G to be searched for the exact code FQR and when
found return the data in the corresponding columns E and D

=VLOOKUP("FQR",D6:G80,2,TRUE)&" "&VLOOKUP("FQR",D6:G80,1,TRUE)

the above gives the wrong answer, and if I use the FALSE array, ie to find
exact match, I get the #N/A error,
Can anybody provide the correct formula

Thanks

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 35,218
Default VLOOKUP puzzle ??

One way...

=if(iserror(vlookup("FQR",d6:g80,3,false)),"No match",
vlookup("FQR",d6:g80,3,false)& " " & vlookup("FQR",d6:g80,2,false))

Remember that if FQR has leading/trailing spaces in D6:D80, then there won't be
a match.

Anthony wrote:

Hi all,
getting a little frustrated with what I though would be a simple VLOOKUP
formula, so turn to you for help.

Cells C6:G80 are populated with data
I want the whole of column G to be searched for the exact code FQR and when
found return the data in the corresponding columns E and D

=VLOOKUP("FQR",D6:G80,2,TRUE)&" "&VLOOKUP("FQR",D6:G80,1,TRUE)

the above gives the wrong answer, and if I use the FALSE array, ie to find
exact match, I get the #N/A error,
Can anybody provide the correct formula

Thanks


--

Dave Peterson
  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 35,218
Default VLOOKUP puzzle ??

I misread your message.

Since you want to match in column G and bring back things to the left of that
key column, you have to use a different formula:

=index(e6:e80,match("fqr",g6:g80,0)) & " " & index(d6:d80,match("fqr",g6:g80,0))

Debra Dalgleish has some notes:
http://www.contextures.com/xlFunctions02.html (for =vlookup())
and
http://www.contextures.com/xlFunctions03.html (for =index(match()))



Anthony wrote:

Hi all,
getting a little frustrated with what I though would be a simple VLOOKUP
formula, so turn to you for help.

Cells C6:G80 are populated with data
I want the whole of column G to be searched for the exact code FQR and when
found return the data in the corresponding columns E and D

=VLOOKUP("FQR",D6:G80,2,TRUE)&" "&VLOOKUP("FQR",D6:G80,1,TRUE)

the above gives the wrong answer, and if I use the FALSE array, ie to find
exact match, I get the #N/A error,
Can anybody provide the correct formula

Thanks


--

Dave Peterson
  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 13
Default VLOOKUP puzzle ??

if your data is in c6:g80, surely you want =vlookup("FQR",C6:G80,2,false) ? you can add the error trap as suggested by Dave P.






"Anthony" wrote in message ...
Hi all,
getting a little frustrated with what I though would be a simple VLOOKUP
formula, so turn to you for help.

Cells C6:G80 are populated with data
I want the whole of column G to be searched for the exact code FQR and when
found return the data in the corresponding columns E and D

=VLOOKUP("FQR",D6:G80,2,TRUE)&" "&VLOOKUP("FQR",D6:G80,1,TRUE)

the above gives the wrong answer, and if I use the FALSE array, ie to find
exact match, I get the #N/A error,
Can anybody provide the correct formula

Thanks



  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 275
Default VLOOKUP puzzle ??

Dave,

I have concatenated columns D and E and placed them into column AA.
In column AB I have a list of codes
I now want to search column AB for the value "FQR" and give the
corresponding name from column AA

I have tried

=VLOOKUP("FQR",AA6:AB100,2,FALSE)

but I get the #N/A response....

any ideas ??

"Dave F" wrote:

In order for FALSE to work your data needs to be sorted in ascending order by
the column you're looking up.

Since you're concatenating two lookups on two separate columns, that is
impossible.

I would suggest that you concatenate columns E & D prior to running the
VLOOKUP, and then have the VLOOKUP lookup in the concatenated column. So,
for example: =VLOOKUP("FQR",D6:H80,2,FALSE)

Dave
--
Brevity is the soul of wit.


"Anthony" wrote:

Hi all,
getting a little frustrated with what I though would be a simple VLOOKUP
formula, so turn to you for help.

Cells C6:G80 are populated with data
I want the whole of column G to be searched for the exact code FQR and when
found return the data in the corresponding columns E and D

=VLOOKUP("FQR",D6:G80,2,TRUE)&" "&VLOOKUP("FQR",D6:G80,1,TRUE)

the above gives the wrong answer, and if I use the FALSE array, ie to find
exact match, I get the #N/A error,
Can anybody provide the correct formula

Thanks

  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 275
Default VLOOKUP puzzle ??

Cheers Dave, thats fixed it

"Dave Peterson" wrote:

One way...

=if(iserror(vlookup("FQR",d6:g80,3,false)),"No match",
vlookup("FQR",d6:g80,3,false)& " " & vlookup("FQR",d6:g80,2,false))

Remember that if FQR has leading/trailing spaces in D6:D80, then there won't be
a match.

Anthony wrote:

Hi all,
getting a little frustrated with what I though would be a simple VLOOKUP
formula, so turn to you for help.

Cells C6:G80 are populated with data
I want the whole of column G to be searched for the exact code FQR and when
found return the data in the corresponding columns E and D

=VLOOKUP("FQR",D6:G80,2,TRUE)&" "&VLOOKUP("FQR",D6:G80,1,TRUE)

the above gives the wrong answer, and if I use the FALSE array, ie to find
exact match, I get the #N/A error,
Can anybody provide the correct formula

Thanks


--

Dave Peterson

  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,574
Default VLOOKUP puzzle ??

I think Dave Peterson has the solution.

Dave
--
Brevity is the soul of wit.


"Anthony" wrote:

Dave,

I have concatenated columns D and E and placed them into column AA.
In column AB I have a list of codes
I now want to search column AB for the value "FQR" and give the
corresponding name from column AA

I have tried

=VLOOKUP("FQR",AA6:AB100,2,FALSE)

but I get the #N/A response....

any ideas ??

"Dave F" wrote:

In order for FALSE to work your data needs to be sorted in ascending order by
the column you're looking up.

Since you're concatenating two lookups on two separate columns, that is
impossible.

I would suggest that you concatenate columns E & D prior to running the
VLOOKUP, and then have the VLOOKUP lookup in the concatenated column. So,
for example: =VLOOKUP("FQR",D6:H80,2,FALSE)

Dave
--
Brevity is the soul of wit.


"Anthony" wrote:

Hi all,
getting a little frustrated with what I though would be a simple VLOOKUP
formula, so turn to you for help.

Cells C6:G80 are populated with data
I want the whole of column G to be searched for the exact code FQR and when
found return the data in the corresponding columns E and D

=VLOOKUP("FQR",D6:G80,2,TRUE)&" "&VLOOKUP("FQR",D6:G80,1,TRUE)

the above gives the wrong answer, and if I use the FALSE array, ie to find
exact match, I get the #N/A error,
Can anybody provide the correct formula

Thanks

  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 12
Default VLOOKUP puzzle ??

You can't lookup a value in a column and then return data in a column to the
left of it using VLOOKUP, hence the match and index solution. Your answer
would work if the lookup value was in column C instead of column G

"jiwolf" wrote:

if your data is in c6:g80, surely you want =vlookup("FQR",C6:G80,2,false) ? you can add the error trap as suggested by Dave P.






"Anthony" wrote in message ...
Hi all,
getting a little frustrated with what I though would be a simple VLOOKUP
formula, so turn to you for help.

Cells C6:G80 are populated with data
I want the whole of column G to be searched for the exact code FQR and when
found return the data in the corresponding columns E and D

=VLOOKUP("FQR",D6:G80,2,TRUE)&" "&VLOOKUP("FQR",D6:G80,1,TRUE)

the above gives the wrong answer, and if I use the FALSE array, ie to find
exact match, I get the #N/A error,
Can anybody provide the correct formula

Thanks

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
VLookup a Vlookup adamb2000 Excel Worksheet Functions 4 June 28th 06 10:54 PM
VLOOKUP Problem Ian Excel Discussion (Misc queries) 3 April 6th 06 06:47 PM
VLOOKUP Limitations chris_manning Excel Worksheet Functions 2 August 9th 05 06:23 PM
vlookup data hidden within worksheet Excel Worksheet Functions 0 January 26th 05 12:09 PM
Vlookup info being used without vlookup table attached? Excel Worksheet Functions 0 January 25th 05 10:43 AM


All times are GMT +1. The time now is 04:17 AM.

Powered by vBulletin® Copyright ©2000 - 2023, Jelsoft Enterprises Ltd.
Copyright 2004-2023 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"