VLOOKUP puzzle ??

[Guest]

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

 

[Guest]

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

 

[Dave Peterson]

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 D680, then there won't be
a match.

 

[Dave Peterson]

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()))

 

[jiwolf]

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.

 

[Guest]

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 ??

 

[Guest]

Cheers Dave, thats fixed it

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 D680, then there won't be
a match.

 

[Guest]

I think Dave Peterson has the solution.

Dave