Email:      Pass:    Pass?
Close Window   
TE
Subscribe for Email Updates!
Excel tips, help, and more!
E-mail:


Advertisements


Free Excel Forum

Lookup Formula Finding Incorrect Results

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

View Answers     

Similar Excel Video Tutorials

Helpful Excel Macros

Vlookup Macro to Return All Matching Results from a Sheet in Excel
- This Excel Macro works like a better Vlookup function because it returns ALL of the matching results. Run the
Excel Macro that Searches Entire Workbook and Returns All Matches
- This is the ultimate Lookup Macro for Excel. It will search every worksheet in the workbook and return all of the mat
Hide Formulas in a Worksheet and Prevent Deletion
- This macro will hide all formulas within a workbook and not allow them to be deleted. The page will not be protected li
Determine if a Cell Contains a Function in Excel - Great for Conditional Formatting and Validation - UDF
- Determine if a cell in Excel contains a formula or function with this UDF (user defined function). This function return
Vlookup Macro to Return All Matching Results and Stack them with Previous Results
- This is very similar to the other Vlookup type Macro in that it returns all of the results that match a particular se

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


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,





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?


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 stuck with finding fuzzy vertical lookup search and I also used Fuzzy lookup Add-in but its taking huge time and its hanging too as I have data around 45 lakhs Rows

Can you help me find fuzzy lookup for the below table. I want to fuzzy lookup with Column A&B with D&E and want to return the results in column G,H,I,J,K respectively.

Google Spreadsheet Link:

https://docs.google.com/spreadsheet/...laE9DSGc#gid=0

Looking forward for your prompt response and thanking you in advance.

Cheers
Kashif

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


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


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


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 V-Lookup 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 V-lookup function. In my second tab I sum totals for all advisors and In the third tab I graph these results using the V-lookup function to find the totals for particular advisors. I am having a problem finding certain listings. When I use the V-lookup, 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!


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


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)