
Lookup Formula Finding Incorrect Results


Search Excel Forum Posts, Tutorials, Macros, Tips, and More
Lookup Formula Finding Incorrect Results  Excel

View Answers


Similar Excel Video Tutorials
Lookup Adding: SUMPRODUCT & SUMIF
 See how to use the SUMPRODUCT & SUMIF functions together to Lookup multiple items and add them in one cell. This method is great when you have man ...
Similar Topics
Oldchippy was kind enough to solve my problem for me, but sadly it just resulted in another problem! Original Thread
Now that I have the subtotals, all I wanted to do is use a lookup to display the results in different group of cells...simple, right?
Except, most of the time it is not returning the correct [result_vector]. I am really stumped. When I close the workbook and reopen it, then redo the formula, it changes which results it displays incorrectly. I have even tried changing the lookup_value and I still get the same results.
Here is a screenshot showing the sheet and the formula with the goofy results: Screenshot
If you notice, on the right, where the results are shown, heading 4472112 is clearly returning the result for 4472111. This is happening over and over again, regardless of heading, and seemingly with rhyme or reason!
Any thoughts on this?
Thank you  YKA
I wanted to use the lookup function to get a numerical value with
reference to my lookup value, which is a text entry. For the setup of
my spread sheet, I could not use vlookup or hlookup. This lookup
function gave me some correct values, but mostly incorrect ones. The
following is my command:
=LOOKUP(DL2,$AV2:$BL2,$BM2:$CC2)
I checked the lookup value and the lookup vector... they are exactly
the same without spacing or spelling errors. Since I got some correct
values and some incorrect ones, I suspected that the lookup function
does not work for text entry. Am I right? Is there any way that I can
fix the problem? Please advice.
Thanks.
Dear all,
i'm using the following formula on cell B1:
=LOOKUP(a$1;Page1!J$12:$J28;Results$G$12:$G$28)
how can i make it so that when i drag the formula in the rows below i get
=LOOKUP(a$1;Page1!K$12:$K28;Results$G$12:$G$28)
=LOOKUP(a$1;Page1!L$12:$L28;Results$G$12:$G$28)
=LOOKUP(a$1;Page1!M$12:$M28;Results$G$12:$G$28)
=LOOKUP(a$1;Page1!N$12:$N28;Results$G$12:$G$28)
=LOOKUP(a$1;Page1!O$12:$O28;Results$G$12:$G$28)
...
and so on?
thanks in advance
I have an Excel workbook with several worksheets. The lookup formulas worked well but we added some rows a few weeks ago and there are a few quirks now.
The general setup of the workbook is pricing calculation worksheets that refer to master pricing worksheets that are updated every price increase.
1) There is a lookup statement that is pulling in an incorrect dollar value into the price calculation sheet. The lookup formula is the same formula in all other cells and the other cells work correctly!! The part number cell is correct.
The lookup function initially looks for a part number and then inserts the price for that part number. Works great in other cells! I have two cells that appear to be pulling in the price for another item with a similiar part number  first letter or first two letters are the same.....
The lookup formula does not appear to taking the first similiar part number it finds (top to bottom) because the incorrect price refers to a part number that appear, sequentially, after the correct part number.
Any ideas why these incorrect prices are being inserted into the cells?
Thanks
Hello,
I am using a CONCATENATE formula to designate an ID number taken from multiple cells.
=CONCATENATE(LEFTB(F2,2), LEFTB(E2,1), LEFTB(B2,3))
Based on the result of that formula i have another sheet where i am using a LOOKUP formula so that when i type in the result of my concatenate formula on the previous page it will fill in information on the current sheet.
=LOOKUP(F5,[NYSTAPWATCH.xlsx]Sheet1!$C$2:$C$33,[NYSTAPWATCH.xlsx]Sheet1!$B$2:$B$33)
my formulas work for some of the ID numbers but others put in incorrect information. I am thinking its because excel isn't designating the results of the first formula as a text, rather a formula. I tried hiding and locking the formula but had the same results. Is there something i can add to my formula to designate to read as text?
I have a simple lookup formula
=LOOKUP(F8,O8:O29,P8:P29)*G8
Where I need to use the value of cell "F8" to determine what I must multiply
"G8" by. Works fine. I need to know how to drag that formula (copy) it to
successive cells. When I do that, Excel advances all the values by one
number e.g. =LOOKUP(F9,O9:O30,P9:P30)*G9. This gives me an incorrect
result. How can I make it advance the cells without advancing the lookup
range? I did a search on Lookup and received a ton of hits, thought I could
narrow it down with a direct question.
TIA
Randy,
Hi all,
I need help with the following. I'm trying to work out a lookup formula that will return multiple results. I have a data file set out the same as below. My lookup will be in another worksheet. My lookup value will be a cell reference that could contain one of the letters in column A below. If it does, then I want to return Column B & C. Problem is I want to continue looking and returning results if it finds my lookup value again and again. Make sense? apologies if my terminogy is not great.
Thanks in advance
e.g
A B C
a 123 3
a 456 5
a 789 7
b 854 5
b 342 2
b 343 23
b 938 28
etc
Not sure which function I should be using. I tried both IF and LOOKUP and failed.
I'm trying to search for values from one worksheet and identify whether or not those values exist in another worksheet. I attempted the following lookup in field A2:
=LOOKUP(B2,Sheet3!A$2:A$914,Sheet3!C$2:C$914)
B2 (thru B5000 or so) contains values I want to search for; sheet3!A$2:A914 is where I want to look and column C of that same sheet, entered the text "Yes" in an attempt to have the results list "Yes" for hits and N/A for misses. (All fields are text.) I copied the formula all the way down the sheet in column A. The result it is returning is N/A in A2 and Yes in A2 to the bottom, which is incorrect.
I have Excel 2000.
I am having trouble with a lookup returning the wrong value (either VLookup or Lookup). The first column is the output of an IF function where desired outcome results in a specific text or number (I've tried both) and the next column is a text value I wish output. The Lookup value is one unique specific text or number in the first column.
The table also contains calculations based on a separate variable which determine the result of the IF statement.
Depending on the variable, the lookup either funtions correctly or it returns the last row's value (even when that is incorrect).
I need suggestions as to why the Lookup doesn't always work correctly.
Thanks
Motorjobs
I am using the vlookup function on a chart that has 5 rows of data. When it looks up the values, some of the results it pulls are correct, and some are the incorrect value. I have checked my formula and it is referencing the correct cell and the chart to look up the values is correct. Any idea why excel is returning the wrong value?
I am using LOOKUP and VLOOKUP to lookup data on tables. I have done this
successfully, but I get unusual results when looking up calculated values.
Sometimes the lookup results are correct sometimes not. All values are
numbers, lookup_vector is in descending order. Lookup works when value is
typed in cell not calculated. Unfortunately I need to lookup the value
calculated from a lookup value. calculation is simple ie =D2+0.1
I am specifically looking up the values less than and greater than the
lookup_value. I have been looking up the less than value then adding 0.1 to
get the greater than value.
Hi All,
I have a problem with name matching.
I have 2 databases that I have inherited both containing the same account names.
I need to combine these names into one summary page/reference page.
My problem arrises where the names have either been spelt wrong or are entered differently.
I.e
DB1
Bob Simmon
DB2
Simmon Bob
I have experimented with:
=IF(ISERROR(LOOKUP(2^15,SEARCH($D2,INDIRECT("Result"&I$1)),Result1)),IF(ISERROR(LOOKUP(2^15,SEARCH(INDIRECT("Result"&I$1),$D2),Result1)),J2,LOOKUP(2^15,SEARCH(INDIRECT("Result"&I$1),$D2),Result1)),LOOKUP(2^15,SEARCH($D2,INDIRECT("Result"&I$1)),Result1))
and many variants/ranges
To a certain level of success but this does through up incorrect results and on occation no results at all.
I am happy to go ptogramaticaly with this, if there are any suggestions.
Thanks
I tried to save some steps in my db by using the same lookup table for a number of questions. In my report, I'm trying to count the results for these questions, but as I try to use the formula ex. =Sum(IIF([Responses]=1,1,0)) to count the results for each question, I' m getting the same results for each question.
I'm trying to avoid making any more lookup tables. Does anyone know if I can get around this? I'm trying to reference the field to the formula as =Sum(IIF([Responses.FieldName]=1,1,0)) .. not going anywhere.
Thanks.
Eileen
First let me say, thank you all who helped me before. Its greatly appreciated. I been workign on my formula further and it is working (no error messages), but the results are still not correct.
The formula is as follows:
=IF(AND(F8="Change",J8"Request for Service",OR(D8={"Completed","Closed"})),"Incorrect Change Reason Code",IF(AND(F8="Information",J8"Request for Information",OR(D8={"Completed","Closed"})),"Incorrect Information Reason Code",IF(AND(F8="Incident",OR(J8={"Request for Service","Request for Information","Complaint"}),OR(D8={"Completed","Closed"})),"Incorrect Incident Reason Code","")))
My current issue is that for Incidents, if the other cell is matched with Request for Service or Request for Information or Complaint or a blank, with the Status "Closed or Completed" I want it to say: Incorrect Incident Reason Code. However its still not working right. Pasted below are the results..
_____________________________________________________
Line , Status, Category, Reason Code
_____________________________________________________
Line 3, Completed, Incident, Request, Incorrect Information Reason Code
Line 4, Completed, Incident, Software failure, Incorrect Information Reason Code
Line 5, In Progress, Incident
Line 6, Completed, Change, Request for service, Incorrect Information Reason Code
Line 7, Completed, Incident, Capacity issue, Incorrect Information Reason Code
Line 8, Completed, Incident, Capacity issue, Incorrect Information Reason Code
Line 9, Completed, Change, Request for service, Incorrect Information Reason Code
________________________________________________________
So, Here are the issues:
Line 3. It should say Incorrect Incident Reason Code since "Request" is not accepeted. It says "Incorrect Information Reason Code.
Line 4. It should just show ...
Completed, Incident, Software failure ..since there are no issues here.
Line 6. This line is also incorrect it should show..
Completed, Change, Request for service
Line 7 & 8. These lines are also incorrect. It should show..
Completed, Incident, Capacity issue
Line 9 . This line is incorrect it shoudl show:
Completed, Change, Request for service
Thanks In Advance...
_________________
SAM I AM..
This is interesting...
this formula, which we are all familiar with,
=VLOOKUP(A6,$J$3:$K$150,2,TRUE)
is gving an incorrect value in a cell, in this case, B6. The value in row A6 is read and is "looked up" in col $J$3:$J$150, and the corresponding value is correctly found and placed in the respective row in col B for all other rows. Recopying and pasting the formula in cells that DO give the correct lookup value still does not work. There is something underlying somewhre that makes it read an incorrect value, but what and where is it ?
Thanks for all your help.
chazrab
CR
Hi all,
Im trying to figure out how to run a lookup that will do what I need.
I have a sheet which has a column of numbers (AK) these numbers are calculated using a formula.
What im trying to do is lookup this column and return information from columns D,J,H,K,L from the same row as the number in AK.
EG if i lookup "3" i may get 200 results and I want the secific info from the cells in the columns mentioned above corresponding to all results.
I hope this makes sense and someone can help
Thanks
Hello,
I am having problems with the VLookup function. I have three tabs on my spreadsheet. THey all build off each other. My problem is with the third tab when I am using the Vlookup function. In my second tab I sum totals for all advisors and In the third tab I graph these results using the Vlookup function to find the totals for particular advisors. I am having a problem finding certain listings. When I use the Vlookup, I am able to find all listings except for the last three. The range seems to be wrong but I am positive this isnt the case. Please let me know if anyone can help me with this problem.
I am trying to work out a formula using LOOKUP that converts letter grades into numbers. Here is a simplified version of it (the original formula got the same results):
=LOOKUP(A1,{"a+","a","a","b+","b","b","c+","c","c","d+","d","d","f";12,11,10,9,8,7,6,5,4,3,2,1,0})
This should get:
A+=12
A=11
A=10
...
...
...
F=0
Instead I get this:
a+ 10
a #N/A
a 10
b+ 7
b 8
b 7
c+ 6
c 7
c 7
d+ 3
d 4
d 4
f 0
Am I not using the LOOKUP function correctly then? Either way, please help!
I think I can best explain my problem by refering to the attached example.
In the results table I have columns with ref nos and codes. Each ref no uses a code to refer to a formula in the lookup table. The formula then uses the relevant values  A,B,C etc from the values table to produce a result in the results column in the results table.
The problem is, how can I get the formula to calculate for two or more sets of values without having to repeat the lookup table for each reference number in the results table?
e.g. ref nos 03 & 06 use the same code (11) and same formula, but the formula needs to refer to different cells for the components A.B,C & d.
Is there a way I can do this without using macros?
Cheers,
Dave2000
My formula L5 does not PRODUCE the Expected Result  Of 5;
I'm sure there is a reason, but I don't at the moment know why it is.
Any help appreciated
Jim
Sheet1
A
B
C
D
E
F
G
H
I
J
K
L
1
Last Value In
Last Value In
2
a
b
c
d
e
f
COLUMNS
ROWS
3
1
5.00
7.00
3.00
15.00
4.00
9.00
9.00
4.00
4
2
3.00
2.00
2.00
3.00
5
3
7.00
8.00
8.00
22.00
<< Why not 5?
6
4
2.00
3.00
22.00
88.00
88.00
88.00
7
5
4.00
4.00
11.00
8
6
5.00
11.00
11.00
9.00
Excel 2007
Worksheet Formulas
Cell
Formula
I3
=LOOKUP( 2,1/( A3:G3<>"" ),A3:G3 )
I4
=LOOKUP( 2,1/( A4:G4<>"" ),A4:G4 )
I5
=LOOKUP( 2,1/( A5:G5<>"" ),A5:G5 )
I6
=LOOKUP( 2,1/( A6:G6<>"" ),A6:G6 )
I7
=LOOKUP( 2,1/( A7:G7<>"" ),A7:G7 )
I8
=LOOKUP( 2,1/( A8:G8<>"" ),A8:G8 )
K3
=LOOKUP( 1,1/( B$3:B$8<>"" ),B$3:B$8 )
K4
=LOOKUP( 1,1/( C$3:C$8<>"" ),C$3:C$8 )
K5
=LOOKUP( 1,1/( D$3:D$8<>"" ),D$3:D$8 )
K6
=LOOKUP( 1,1/( E$3:E$8<>"" ),E$3:E$8 )
K7
=LOOKUP( 1,1/( F$3:F$8<>"" ),F$3:F$8 )
K8
=LOOKUP( 1,1/( G$3:G$8<>"" ),G$3:G$8 )
I've attached the problem WS and referenced WS.
I have a lookup function which is working in some cells but either returning a N/A or incorrect value in others. The only difference in the forumula is the lookup value (ie cell A11, A13, etc) I've made sure that the format of the lookup value matches the lookup range as well as reentered the data, so for nothing.
I am completely baffled and out of ideas.
HI
how can I get excel to count all each individual word or no. from the contents of table below, without having to specifically tell it what to count e.g count glassware etc?
I want a summary table of how many of each word and no. contained in the table, so I can buld a histogram.
regards
tcol
1
collect glassware for sample & std including pippettes
incorrect pippette volume
High OOS/OOT results reported
5
incorrect std prep
5
Standard Accuracy Check
1
25
25
1
collect glassware for sample & std including pippettes
incorrect pippette volume
High OOS/OOT results reported
5
incorrect sample prep
5
Internal Std
1
25
25
1
collect glassware for sample & std including pippettes
incorrect pippette volume
Low OOS/OOT results reported
5
incorrect std prep
5
Standard Accuracy Check
1
25
25
1
collect glassware for sample & std including pippettes
incorrect pippette volume
Low OOS/OOT results reported
5
incorrect sample prep
5
Internal Std
1
25
25
1
collect glassware for sample & std including pippettes
incorrect volume volumetric flask
High OOS/OOT results reported
5
incorrect std prep
3
Internal Std
1
15
15
1
collect glassware for sample & std including pippettes
incorrect volume volumetric flask
Low OOS/OOT results reported
5
incorrect std prep
3
Internal Std
1
15
15
1
collect glassware for sample & std including pippettes
dirty/contaminated or damaged test tubes (and caps) or pippettes
High OOS/OOT results reported
5
cracked glassware
2
Chemist initialvisual check/Initial Investigation
3
30
10
1
collect glassware for sample & std including pippettes
dirty/contaminated or damaged test tubes (and caps) or pippettes
High OOS/OOT results reported
5
FK506 contaminated glassware
1
Washing Procedures/Chemist visual check/Retest x 4
5
25
5
1
collect glassware for sample & std including pippettes
hot pippettes, test tubes
High OOS/OOT results reported
5
incorrect std volume transferred
1
Chemist training
5
25
5
1
collect glassware for sample & std including pippettes
hot pippettes, test tubes
Low OOS/OOT results reported
5
incorrect sample volume transferred
1
Chemist training
5
25
5
1
collect glassware for sample & std including pippettes
wet pippettes, test tubes
Low OOS/OOT results reported
5
diluted soln transferred
1
Visual Check by washroom staff and chemist
3
15
5
1
collect glassware for sample & std including pippettes
incorrect pippette volume
invalid analysis
1
incorrect sample prep
5
Internal Std
1
5
5
1
collect glassware for sample & std including pippettes
incorrect pippette volume
invalid analysis
1
incorrect std prep
5
Standard Accuracy Check
1
5
5
1
collect glassware for sample & std including pippettes
dirty/contaminated or damaged test tubes (and caps) or pippettes
invalid analysis
1
poor sample chromotography
3
Review by Chemist/Checker/Approver
1
3
3
Hi,
I have an excel spreadsheet that involves a rather large formula that uses IF VLOOKUP it returns False in the cell when no data has been entered in another cell. I now need to use the function ISNA to make the cell blank  the formula is now even larger and syntactically incorrect. Can someone please help me correct it. The formula in English is If X1="A" then lookup X1 in the range R2:T30, if X1="B" then lookup X1 in the range U2:W30, if X1="C" then lookup X1 in the range Y2:AA30. I've got this to work. How do I add the ISNA fuction? Any help would be gratly appreciated.
Regards
geor68
I have named two columns each with three rows. Col one is Type, from A1 to A4 including the name. (Payment types a Check = cc, Cash = ca, Check = ck. A2 is cc, A3 is ca, A4 is ck. Col two is Pmt, from B1 to B4 including the name. B2 is $100, B3 is $50, B4 is $25.
In the Payment Type section I have three rows: A7 is cc, A8 is ca, A9 is ck. I typed a Lookup formula in B7, B8 and B9. B7 is =Lookup("cc",Type,Pmt). B8 is =Lookup("ca",Type,Pmt). B9 is =Lookup("ck",Type,Pmt). B8 returns $50, B9 returns $25 but B7 returns $50. The only way I can get B7 to return the correct value, $100 for "cc" is to include B6, one cell above the correct value. I tried using cell ranges instead of names and the formula skips the first row of values in the array. I've never encountered this before. Any ideas greatly appreciated.
A1 Type B1 Pmt
A2 cc B2 $100
A3 ca B3 $ 50
A4 ck B4 $ 25
Payment Type
B7 cc =Lookup("cc",A2:B4) returns $50 (incorrect)
B8 ca =Lookup("ca",A2:B4) returns $50 (correct)
B9 ck =Lookup("ck",A2:B4) returns $25 (correct)
How do I get a value associated with the number of rows displayed after an advanced filter is applied.
I have tried things like counta($B$2:$B:$225) and it gives me the the results expected. However, when I reapply a filter on the same data again the forumla changes itself to count($B$2:$B$2) giving the incorrect results.
Is there a more reliable formula, or how do I keep the existing formula to retain itself?
Jenn

