Home 
Search 
Today's Posts 
#1
Posted to microsoft.public.excel.worksheet.functions




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
Posted to microsoft.public.excel.worksheet.functions




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
Posted to microsoft.public.excel.worksheet.functions




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
Posted to microsoft.public.excel.worksheet.functions




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
Posted to microsoft.public.excel.worksheet.functions




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
Posted to microsoft.public.excel.worksheet.functions




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
Posted to microsoft.public.excel.worksheet.functions




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
Posted to microsoft.public.excel.worksheet.functions




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
Posted to microsoft.public.excel.worksheet.functions




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 
Display Modes  


Similar Threads  
Thread  Forum  
VLookup a Vlookup  Excel Worksheet Functions  
VLOOKUP Problem  Excel Discussion (Misc queries)  
VLOOKUP Limitations  Excel Worksheet Functions  
vlookup data hidden within worksheet  Excel Worksheet Functions  
Vlookup info being used without vlookup table attached?  Excel Worksheet Functions 