Lookup Formula Finding Incorrect Results

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

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