Lookup formula returning same value for multiple lookup values

I am using a formula to retrieve information from one worksheet to
another.

To simplify my issue, see if you can understand the example below:

1. Open a new worksheet
2. In Column A, enter the numbers 1 thru 9 down the column
3. In Column B, enter the numbers A thru I down the column
4. In Column F, list the following numbers down the column: 5, 9, 4,
12, 6, 7, 22, 3, 450
5. In Column G, enter the formula: =LOOKUP(F1,$A$1:$A$9,$B$1:$B$9)
6. Copy the formula down and look at the results.

For the values 12, 22 and 450, the logic should have failed, but
returns the bottom-most value from the return value array.

Basically, I am trying to compare two lists, and when an item does not
appear in the first list, it should return a blank value. If the item
does appear, then it should return the corresponding value for a cell
on the sheet.

Any ideas?

Thanks for you help!

Take care.

RPW


-- 
wellsrp
------------------------------------------------------------------------
wellsrp's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=27974
View this thread: http://www.excelforum.com/showthread.php?threadid=474764

0
10/10/2005 2:36:31 PM
excel 39879 articles. 2 followers. Follow

3 Replies
285 Views

Similar Articles

[PageSpeed] 51

Instead of using LOOKUP, use VLOOKUP and check for FALSE.  If FALSE
return a blank (""), as such:

=IF(ISNA(VLOOKUP(F1,$A$1:$B$9,2,0)),"",VLOOKUP(F1,$A$1:$B$9,2,0))

note the "0" in the formula is the same as FALSE, meaning if an exac
match isn't found, #N/A will be returned.  The IF checks for this erro
and returns a blank if the error is found.  If no error, then return th
value in the second column (col B).

HT

--
swatsp0

-----------------------------------------------------------------------
swatsp0p's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1510
View this thread: http://www.excelforum.com/showthread.php?threadid=47476

0
10/10/2005 4:03:29 PM
Here's one way:

=IF(COUNTIF($A$1:$A$9,F1)=0,"",(LOOKUP(F1,$A$1:$A$9,$B$1:$B$9)))

Best Regards,
Paul

"wellsrp" <wellsrp.1woyya_1128956701.8708@excelforum-nospam.com> wrote in 
message news:wellsrp.1woyya_1128956701.8708@excelforum-nospam.com...
>
> I am using a formula to retrieve information from one worksheet to
> another.
>
> To simplify my issue, see if you can understand the example below:
>
> 1. Open a new worksheet
> 2. In Column A, enter the numbers 1 thru 9 down the column
> 3. In Column B, enter the numbers A thru I down the column
> 4. In Column F, list the following numbers down the column: 5, 9, 4,
> 12, 6, 7, 22, 3, 450
> 5. In Column G, enter the formula: =LOOKUP(F1,$A$1:$A$9,$B$1:$B$9)
> 6. Copy the formula down and look at the results.
>
> For the values 12, 22 and 450, the logic should have failed, but
> returns the bottom-most value from the return value array.
>
> Basically, I am trying to compare two lists, and when an item does not
> appear in the first list, it should return a blank value. If the item
> does appear, then it should return the corresponding value for a cell
> on the sheet.
>
> Any ideas?
>
> Thanks for you help!
>
> Take care.
>
> RPW
>
>
> -- 
> wellsrp
> ------------------------------------------------------------------------
> wellsrp's Profile: 
> http://www.excelforum.com/member.php?action=getinfo&userid=27974
> View this thread: http://www.excelforum.com/showthread.php?threadid=474764
> 


0
pclive (112)
10/10/2005 4:08:02 PM
Hi RPW,

Maybe this will do what you want.

=IF(ISNA(VLOOKUP(F1,$A$1:$B$9,2,0)),0,VLOOKUP(F1,$A$1:$B$9,2,0))

HTH
Regards,
Howard

"wellsrp" <wellsrp.1woyya_1128956701.8708@excelforum-nospam.com> wrote in 
message news:wellsrp.1woyya_1128956701.8708@excelforum-nospam.com...
>
> I am using a formula to retrieve information from one worksheet to
> another.
>
> To simplify my issue, see if you can understand the example below:
>
> 1. Open a new worksheet
> 2. In Column A, enter the numbers 1 thru 9 down the column
> 3. In Column B, enter the numbers A thru I down the column
> 4. In Column F, list the following numbers down the column: 5, 9, 4,
> 12, 6, 7, 22, 3, 450
> 5. In Column G, enter the formula: =LOOKUP(F1,$A$1:$A$9,$B$1:$B$9)
> 6. Copy the formula down and look at the results.
>
> For the values 12, 22 and 450, the logic should have failed, but
> returns the bottom-most value from the return value array.
>
> Basically, I am trying to compare two lists, and when an item does not
> appear in the first list, it should return a blank value. If the item
> does appear, then it should return the corresponding value for a cell
> on the sheet.
>
> Any ideas?
>
> Thanks for you help!
>
> Take care.
>
> RPW
>
>
> -- 
> wellsrp
> ------------------------------------------------------------------------
> wellsrp's Profile: 
> http://www.excelforum.com/member.php?action=getinfo&userid=27974
> View this thread: http://www.excelforum.com/showthread.php?threadid=474764
> 


0
lhkittle (223)
10/10/2005 4:12:29 PM
Reply:

Similar Artilces:

Change Item Lookup Code to Uppercase
Having issues scanning barcodes that are generated from RMS when the Item Lookup Code is in lowercase. I have changed a few items in HQ manager that had lowercase Lookup Codes to uppercase and after I synced the SO Database and printed off a new Label and the barcode scans fine. So now I am wondering if I can just generate a SQL Query with the Item lookup Code and Item ID and export it to Excel. Use Excels UPPER Formula to change all Item Lookup Codes to Uppercase and then export the data into the HQ Database. This seems a little far-fetched but it seems like it could work. Just would l...

problems printing multiple copies on one page, only prints 1
Hi there I am trying to print four copies on one piece of A4 paper, the system and printer resizes the document and prints it in the top left hand corner, but no other appear on the same page Verify your work area is � or less the size of your paper stock. When you go to print select File Print Page Options "Print multiple copies per sheet." -- Don Vancouver, USA "Gary" <Gary@discussions.microsoft.com> wrote in message news:7412DEB1-B05A-427C-9D42-6C71E85A23F0@microsoft.com... > Hi there I am trying to print four copies on one piece of A4 pap...

How to return a specific record for editing?
I have a switchboard that has a selection Edit Record. Once chosen I would like a parameter that asks which record number the user wants to edit. Once typed I would like that specific record to show up in a previously built form. Then once edited I want the form to close and the user returned to the switchboard. I am hung-up big time on this. If anyone has a solution could they be very basic in description since I am very new to this and pretty much need to be walked through it step by step. Thanks ahead of time for your help!!! Use the Where argument of the OpenForm method to open the form ...

sumif formula required
Hello I have only basic knowledge of excel and need a formula to extrac information. A B C Client Process Sqm CEK Fibre 100 RDF Gloss 200 ERF Emul 150 CEK Fibre 100 What formula should I use to extract all CEK clients who purchase th fibre process giving total sqm. I would be extremely grateful to anyone who...

adding multiple contacts at one time from a received email
Is there a way to add multiple contacts from the header of an email sent to me as part of a distribution group? I know I can right-click on them one-at-a-time and do "Add to contacts..." but that's a nuisance for 50+ addresses. Is there a way to do this in bulk? for a reply. thanks. Liz I don't think so. -- Judy Gleeson MVP Outlook Outlook trainer and author of Productiv_IT with Outlook www.acorntraining.com.au <lizzoob@gmail.com> wrote in message news:1162228944.601011.176680@e3g2000cwe.googlegroups.com... > Is there a way to add multiple contacts from t...

Can multiple versions of Money coexist on a PC?
I'm running Money 2004 but have a copy of M 2005 someone gave me. WIth all I've read here I don't want to replace M 2004 with M 2005. I'd like to run them in parallel until I see if I prefer the newer version, based on all I've read here. Is it possible to install M 2005 on a machine with M 2004 and have them run separately? Yes you can. You should install 2005 in a different directory to 2004. Downloads direct from your bank will go to the newer version by default. When you run Money, you need to make sure you open the correct file with the correct version - ie do...

Multiple Google Calendars and Outlook
I run an app called Google Calendar Sync on my PC. It sync Outlook 2010's calendar with that of the Google one. I've since created a second calendar in Google to try and separate work events from social ones. However, Outlook 2010 and this sync program can't see the second or subsequent calendars. I was hoping that someone here has a way or has seen a way to get Outlook to sync with multiple Google calendars. Thanks Hello Marts, I can do it for you. Please reply if you want it. Regards Andrej > I run an app called Google Calendar Sync on my PC. It sync Outlook 2010&#...

Not enough room on Values and X axis labels
I have a stacked bar chart that I created by the chart wizard but now when I try to change the source data it cuts off part of the values and x axis labels so I cannot change these and I cannot add anything to the values or x axis labels. I need to get rid of one of the columns in the chart. Do I have to redo the chart via the wizard again or is there a setting or a way to change the values and labels? Thanks, -- D ...

"Average" calculation formula needed
Hello, I have Excel 97, and am trying to learn formulas. I need to find the "average" of certain values in column B below. This is an example of my worksheet A B 1 TRUE 1.0% 2 FALSE -.8 3 TRUE -.5 4 FALSE -. 5 TRUE 1. 6 TRUE -. 7 FALSE . What I need: In Cell C50 I want the calculated result in the form of a "%". FIRST finds the "TRUE" ocurrances in column "A", then calculate the average % (both positive and negative) in column "B" that are next to "T...

Excel Formula #8
I currently have a spreadsheet set up to record staff holidays or sickness. But i would like the column AG to total how many times the letter S appears in any of 31 cells from colums B to AF. Each cell may have either nothing entered into it or S or H. Thanks for any help Hi Sharon Try =COUNTIF(B2:AF2,"S") -- Regards Roger Govier "Sharon" <Sharon@discussions.microsoft.com> wrote in message news:399B5039-CAC0-4428-BBB9-18C2C860E564@microsoft.com... >I currently have a spreadsheet set up to record staff holidays or >sickness. > But i would like ...

Array Lookup
I have an array with 8 colums and 7 rows. Each column and each row contains a range of values (for example, a column heading is ">70% and <= 75%", and a row heading is ">=680 and <700") and I need to use the data in the cell in another formula. How do I write the array formula to look up the data that I need for the other formula? The value for which column to use is a result of a Loan-To-Value calculation in another cell in the worksheet, and the row would be from a customer's credit score that would be entered in another cell in the worksheet. T...

VLOOKUP formula #4
I have a workbook with 2 sheets - sheet 1 and sheet 2. I have a blank column in sheet 1 (B), which I want to fill with info from column B in sheet 2, but ONLY is the info in column A in sheet 2 matches with info in column C in sheet 1. I think I should be using VLOOKUP and had a go with the following formula =IF('A20'!A2=('Course Notes'!C5),VLOOKUP('A20'!A2,'A20'!A1:D4467,2)) But it didn't work. Can someone please help or point me in the right direction? Hi try =IF(ISNA(MATCH(A20,'A20'!C2:C4467,0)),"no match",INDEX('A20'!B2:B...

CSV (Comma Separated Values) delimiter ?
Hi, My app exports data in CSV format, using comma separated values. A .csv file is associated with Excel by default and clicking on it opens it up in Excel. However, the delimiter depends on regional settings. Using a comma when regional settings are for the Netherlands, fails because it expects a semicolon then. For USA it is a comma. This information should be stored somewhere in the windows registry. Can anyone tell me where I can find what delimiter is being used on that PC? Lisa >>Can anyone tell me where I can find what delimiter is being used on that PC? Application.Inter...

FORMULA HELP! Find all values in column at random rows and perform calcutlation.
ex. A 1 2 3 3.9% 4 5 6 -2.7% 7 8 9 6.5% 10 11 12 1.2% .. .. .. 1000 Total of formula here. I don't know what row a % number will show up on col A but it is before row 1000. I would like row 1000 under column A to have a formula that says look for any row in column A and if you find a number, add 1 to that number and then find the next one and add 1 to that number and then multiply those two together and then find the next one add 1 then multiply to the last set...etc. Bascially, A100 would have the formula (1+A3)*(1+A6)*(1+A9)*(1+A12)...e...

Replace variable row number in formulas
What I'm looking for is a macro that will replace the row number in a formula with a new number that I designate through an input box. Below is the code I have thus far. The problem lies in how I'm defining the integer portion of the formula that I want to replace - vbInteger (or vbLong) don't seem to work. Then finally I need to set Section 2 in a loop through Column CN. Any advice would be greatly appreciated! --------------------------------------------------------------------------------------------------------------------------- Sub UpdateFormulas_2() Dim LRowNumber ...

Ignor zero values
This is a form of a common query, but I haven't been able to nail down an answer... I have a simple graph, the data series are formulae calculating data from another source. The catch is, I don't want the graph to plot zero values! Tools>Options>Graphs>Plot Empty Cells does not work because there are formulae in the cells. Suggestions to utilise error symbols don't work because I subsequently reference the data series for further calculations. Any thoughts anyone? (Running Excel 2002 SP3) You can use array formulas (array entered with Ctrl-Shift-Enter) to stri...

Break Even, Time to Value, IRR, What do all these things mean?!?
I'm having trouble searching for this, because I am not sure what to call it. What I have is two different solutions that will cost me money. One of them has a higher cost up front, but a lower monthly cost, and the other has a lower cost up front, but higher monthly cost. What I'm interested in knowing is what the break even point is. Where do they intersect. If I choose the initially more costly solution, how many months (days, years, whatever) must I use it before it is a better overall choice over the other? For example. Solution A costs $60/month, and $368 up front. Soluti...

Need Formula for control the random
Hi --1-- I've x-cells each with same random formula --2-- Those x-random numbers goes to x-seperated cells --3-- In groups of 6 i need different numbers in those x-cells So i need formula to check the seperated cells of different numbers. (so 2 checks 1/3 checks 2/4 checks 3 etc...) How can i : Let cell2 know to check the random number in cell1 ....?? ((if cell1=cell2 then run random again in cell2)) els next cell.. If cell3=cell2 then run random cell3 afgain..else go to cell4 etc How can i put this together in a correct formula ?? random formula together with the (check formula) ...

Display Multiple images
SGksDQogICAgICBKdXN0IHdhbnQgdG8gY2hlY2sgd2hldGhlciBpcyBpdCBwb3NzaWJsZSB0byBk aXNwbGF5IG11bHRpcGxlIGltYWdlcyB1c2luZyBTREkgaW5zdGVhZCBvZg0KICAgICBNREk/DQog ICAgIElmIHllcywgYW55IGhpbnQgb24gaG93IHRvIGJlZ2luPw0KIA0KUmVncmRzDQpMZW8NCg== #LEO CHIN SIM# wrote: > > Hi, > Just want to check whether is it possible to display multiple images using SDI instead of > MDI? > If yes, any hint on how to begin? > > Regrds > Leo It is possible to display anything using SDI. You can paint multiple images on one view, or you can use splitter windows and display somethin...

Balance sheet Formula #2
Hello everyone. I got the template from MS for the blance sheet but I ran out of space on the template. Does anyone know what the formula would be in Excel 2002. I want to show Recevable, Payables, and Current balance like the template at the bottom somewhere. Thanks, Tom ...

Using Named Range in LOOKUP formula?
My LOOKUP formula currently reads: =((LOOKUP($I6,$A$2:$A$7,$D$2:$D$7)-J6)+(LOOKUP($I6,$A$2:$A$7,$G$2:$G $7))) I would like to change this so the ranges are columns in a Named Range. Something like: =((LOOKUP($I6,Range_Col1,Range_Col4)-J6)+ (LOOKUP($I6,Range_Col1,Range_Col7))) The range name will be selected in a drop-down (data validation) in another cell. So "Range_Col1" actually needs to be referenced from the value selected in K10. Can this be done with formulas? Or do I need a macro? Ed Assuming your named range refers to A1:G7 You can use an expression like this: INDEX...

Setting category to multiple register entries
I've just begun using Money '04 (have been using Quicken for about 6 years), & have dl'd hundreds of transactions from my bank & primary credit card into Money. Is there any way to select a number of transactions & assign them to a given category as a group (e.g., the many restaurant charges on my CC would go to food/dining out)? Similarly, how can I reconcile/clear an entire group of checks? having to do all these one by one is a real PITA, but I don't see a way to select a number of transactions for modification at once. TIA Dan You could use the find/...

Changing Formulas a simpler way?
I have a workbook with 13 worksheets: labeled January-December, and on more worksheet that is linked to a particular month (to get a overview). An exmaple of this formula from that overview workshee looks like this: =IF(NOT(ISERROR(INDEX(June!$B$2:$L$418,MATCH($A3,June!$G$2:$G$364,0),4))),INDEX(June!$B$2:$L$418,MATCH($A3,June!$G$2:$G$364,0),4),"")&IF($K3=0,"No Found","") The function of this formula is not important, but as you can see...it a big hassle for me to change June to July. Currently I am doin "Control H" to replace "june" to ...

Multiple users with Outlook Client for Desktop
As I read through the set up instruction for the desktop client, it reads that as long as each user on the pc have an Outlook account set up, they will all use the CRM installation, yet I have installed CRM on a pc and I am the only one that see CRM in my Outlook? Is there something missing from setup that needs to be done? Each user (yes - each user) has to install the CRM client on the PC "Onetreeup" <Onetreeup@discussions.microsoft.com> wrote in message news:B1AE26D3-84F4-48BA-AAA5-A0E72A92FCB4@microsoft.com... > As I read through the set up instruction for the de...

how to get more than one highest values
For example, we want to add the best two values A B C D E 1 7 9 4 2 ---> it will add 7 and 9 Cheers. EggHeadCafe.com - .NET Developer Portal of Choice http://www.eggheadcafe.com shaki wrote: > For example, we want to add the best two values > > A B C D E > 1 7 9 4 2 ---> it will add 7 and 9 > > > Cheers. > > EggHeadCafe.com - .NET Developer Portal of Choice > http://www.eggheadcafe.com =LARGE(A1:E1,1) + LARGE(A1:E1,2) is one way Ken Johnson And... =SUM(LARGE(A1:E1,{1,2})) is another. Ken Johnson Hello, =LARGE(A1:E1,1)+LARGE(A1:E1,2) or...