I need to return one of three answers in a cell
I need to have one of three results show up in the cell.
I am using VLOOKUP to get my data result, but I need the cell to display "unknown" if the requested data is not valid. However to add to that, I need the cell to display nothing at all if there is no data being requested. Does anyone have any suggestions please? |
I need to return one of three answers in a cell
Say your lookup table is in D1:E10
=IF(A1="","",IF(ISNA(MATCH(A1,D1:D10)),"unknown",V LOOKUP(A1:D1:E10,2,0))) HTH -- AP "Kath" a écrit dans le message de news: ... I need to have one of three results show up in the cell. I am using VLOOKUP to get my data result, but I need the cell to display "unknown" if the requested data is not valid. However to add to that, I need the cell to display nothing at all if there is no data being requested. Does anyone have any suggestions please? |
I need to return one of three answers in a cell
Can you give us a little more information. Unknown can be added to a lookup as the valure to return at he top or bottom of the range of if(isblank(a1)," ",if(vlookup you have used=#n/a,"Unknown",vlookup you have used)) Regards Dav -- Dav ------------------------------------------------------------------------ Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107 View this thread: http://www.excelforum.com/showthread...hreadid=557777 |
I need to return one of three answers in a cell
Thanks. I have used the formula now of
=IF(ISBLANK(D19),"",IF(ISERROR(VLOOKUP(D19,Catalog ue!$A$4:$C$42,2,FALSE)),"unknown",VLOOKUP(D19,Cata logue!$A$4:$C$42,2,FALSE))) and it returns the correct results, but it is saying it is an inconsistant formula. Any ideas on that? Thanks again Kath "Dav" wrote: Can you give us a little more information. Unknown can be added to a lookup as the valure to return at he top or bottom of the range of if(isblank(a1)," ",if(vlookup you have used=#n/a,"Unknown",vlookup you have used)) Regards Dav -- Dav ------------------------------------------------------------------------ Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107 View this thread: http://www.excelforum.com/showthread...hreadid=557777 |
I need to return one of three answers in a cell
My mistake - not an inconsistant formula - just an error of refering to empty
cells - easily fixed by turning that error checker off. thanks very much Dav and Ardus Petus. Greatly appreciated. Kath "Kath" wrote: Thanks. I have used the formula now of =IF(ISBLANK(D19),"",IF(ISERROR(VLOOKUP(D19,Catalog ue!$A$4:$C$42,2,FALSE)),"unknown",VLOOKUP(D19,Cata logue!$A$4:$C$42,2,FALSE))) and it returns the correct results, but it is saying it is an inconsistant formula. Any ideas on that? Thanks again Kath "Dav" wrote: Can you give us a little more information. Unknown can be added to a lookup as the valure to return at he top or bottom of the range of if(isblank(a1)," ",if(vlookup you have used=#n/a,"Unknown",vlookup you have used)) Regards Dav -- Dav ------------------------------------------------------------------------ Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107 View this thread: http://www.excelforum.com/showthread...hreadid=557777 |
All times are GMT +1. The time now is 08:10 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com