See the attached example for an illustration of my problem. Essentially, I am trying to reference the first two numbers of an account number when using VLOOKUP. The function then references a table with account classifications based upon the first two numbers of each 10 digit account. This seems so basic, but is driving me up the wall!
View 3 RepliesI am trying to use the vlookup function but the range of reference has that little green triangle in the corner and if I f2 each cell that triangle goes away and the vlookup function works great. Do you know what is going on here? And also, a quick way to convert all the cells with the green triangle to be without the triangle in a whole range? I don't want to have to go to each cell individually and f2. I've attached an example worksheet for reference to what I am describing. vlookup format.xlsx
View 2 Replies View RelatedFunction sequence giving me "N/A": =VLOOKUP(LEFT(C6,5),H:I,2,FALSE)
Basically, I have numbers that each start with a unique sequence. The first 5 numbers of that sequence represent a certain cell carrier.
What I want to do is have the function look up the first 5 characters of a cell and depending on the 5 characters, I want it to return a certain value.
My idea with the vlookup was to have the lookup value be the first 5 digits and then in my table, it would take only those 5 digits and return a value I have specified in the second column.
I'm working on a spreadsheet for a gaming community. This sheet is used by more than 3000+ players and growing. It has to be updated roughly twice a week for new inventory items, which can range from 1 to 3 new items weekly.
To make it easier on new and existing members, I want to create a way to make updates with the spreadsheet easier and to allow players an easier way of updating the spreadsheet rather than re-entering all the data over and over again twice a week.
So, I started to use VLOOK in some formulas to look at the data and pull the corresponding information. However, this same information is used throughout the sheet and referencing this information has brought about #VALUE errors that I cannot seem to figure out how to get around.
I've included a copy of the spreadsheet to download and review in hopes of finding a solution.
Here's what this speadsheet is doing:
MyInventory is where the players will enter all their items, with some being drop-down selections and manual entry.
The LookupedInventory worksheet is where the VLOOKUP (columns C,G,J,Q,etc.) formula is used to look up entries in the MyInventory worksheet and pull their values over to the LookupedInventory worksheet.
Now these values from the LookupedInventory will be carried over into two other worksheets, InventoryTableAttack and InventoryTableDefense. These two worksheets are where my #VALUE errors are appearing. If the cell referenced is empty, the #VALUE error appears. However, if the cell references a number, the rest of the formulas tied to to this cell reference work. However, since there are errors, the rest of the worksheet cannot function until I fix all the #VALUE errors.
So, what I'm seeking is to see where my issue is and what I need to do to fix it.
If I need to take a different approach to this, please let me know what that is and how to go about doing it.
I've even thought about a macros that copies and paste all the current data from an older spreadsheet to the new one, but I'm not that good at macros. If I can stay away from macros, that would be good, but if not, then I'll have to dig in my heels and start leaning.
I am trying to get my formula to reference directly to the right of the look up value but it keeps returning the diagonal value that is located to the right and up one cell.
excel 1.jpg
excel 2.jpg
I have a large table with data to which I want to retrieve a name to the left of a certain cell.
Clear as mud so far!
I've put together a small example of what I'm after. In cell F9 is the MAX of cells F2:F7. In cell H3 I then want a formula that will find the MAX of F2:F7 and return the text five rows to the left of that cell. In this example it would be Fred.
The second part of this question is how could I get it to display if more than one person had the same total....
I’m trying to write a custom function that always references the cell above it but I can’t figure out the proper syntax to do so i.e.
Function Multiple_Cell_Above()
Multiple_Cell_Above = cellabove * 10
End function
I have a spreadsheet where I extract the last 3 digits of a five digit string by using the "=Right(...)" command.
Column A 71500
Column B =right(column a,3)
Column C 500
Column D = lookup(Column C,toc_desc)
Column E #N/A (if it worked, this would read Labor)
I want to take the result of that command (Column C) and use the "Lookup(...) command to translate type of cost as a name rather than the three digit number (Column D). Excel doesn't recognize the result of the "=Right(...)" command. I am getting #N/A as a result. (Column E). How can I get Excel to read that number and give me the results I need?
So my issue this time is that I have a bunch of columns that correspond to a month. If the user wants to view data from that month column then they have to link to that cell specifically (the worksheet being used is different than the one in which the month columns are)
My goal was to create a single cell in which the user typed in a month abbreviation such as jan, feb, mar.. so on and so forth. From there they correspond to a vlookup table which tells you which month is in what column (because that part never changes) so jan is in column c and feb is in column d... continuing until december is in column n. So the vlookup corresponds to the table and that works fine, type jan into the cell and the vlookup returns c. So i think this will work.
My problem is that in the cell I am typing this in I want it to be a part of a whole cell reference such as c5 or d7 where the 5 or 7 is a constant (I have that part down) however the reference is to another sheet. So in sheet 2 I want to reference sheet 1 i.e.
Formula: [Code].....
<-- is the desired return.
Right now (in sheet 2) I have
Formula: [Select Code] .....
Which returns the desired d5 but as text and not referencing sheet one. I tried
Formula: [Code]....
But this makes the vlookup malfunction (i think it starts looking in sheet 1 for the month cell but it is not and can not be there.
SO how do i force a vlookup to turn into a cell reference, and how do i make that cell reference come from a different worksheet without causing the vlookup to malfunction.
Attached File : examples.xlsx‎
I have a 2010 excel sheet containing 14 columns and 45082 rows in total. I am quite illiterate when it comes to writing macros but I know that what I need can be achieved with a set of codes.
To be more clear, I inserted two tables below. The first one represents the current data structure, and the second one is the way I want my data to look like.
Current data structure looks like
Variable 1
Variable 2
Variable 3
[Code].....
I have a simple VLOOKUP that I can't manage to give the right answer. The contditions must be 'FALSE' as the the stock report database has so many similar numbers in it.
View 2 Replies View RelatedIs it possible to make VLOOKUP look to the left?
Imagine following arrangement of data:
Address Name PIN
A-street Abe 4587
B-street Bob 8214
V-street Val 9657
I want to know the name of the person who has PIN 4587. Is it possible to do this without rearranging the columns?
I ahve got the below range.what I want is if the first chracter starts with any of the below corresponnding value to be shown.I am using left and abs for this . but when the chracter stars with "N" or "E" . i am getting and error.
6Connect
NExplore
1Entry
3Connect
2Entry
5Live
EAchieve
7Live
8Live
9 connect
trying to return a value left of a column instead of from the right.
ex
A B C D
1103013v2200172b
I want to lookup a value in column C and return the value from column A
What I need is to find a max value within a range and then tell me what the row value in Column A is. Usually use VLOOKUP for this but this doesn't like minus numbers.
I think it has something to do with Match and Offset but can't get it to work properly.
I have attached an example, where the max of the total is 4,435 and belongs to Steve but how do I get this to do using formulas
generally vlookup function gives a value from the right side cell. is it is possible to display a cell from the left side. in the attached excel file i want the numbers against the cell which contains mom.....
View 2 Replies View RelatedLEFT(C4,3)
VLOOKUP(C4,C4:D38,2,FALSE)
VLOOKUP(LEFT(C4,3),C4:E38,2,FALSE)
I combined the first two formulas into one (the third one) but it is not working. I get an N/A result.
I have a issue with VLOOKUP and LEFT STRING ETC I have a lookup table see below
AB10 Grampian
AB12 Grampian
AL8 Anglia West
AL9 Carlton LWT
B1 Central West
B10 Central West
B11 Central West
B13 Central West
B14 Central West
B15 Central West
I have a challenge with Vlookups!
I have one workbook called Ratecards, which contains various tabs which are named based on a client code. e.g. ABC02
I then have another workbook which has employees in column B and the client code in C3.
The current Vlookup is =VLOOKUP(B8,'[ratecards.xlsx]ABC02'!$A$5:$N$168,4,false) which works fine for one client.
I want to substitute the ABC02 in the vlookup for cell ref C3, as the contents of C3 is variable and I want it to tell the sheet which ratecard to refer to.
I am trying to find out whether it is possible to reference to a sheet index number, instead of sheet name, in a VLOOKUP formula.
For instance, normally I would write:
VLOOKUP(A1,'ExampleSheet'!A1:B10,2,False)
(I hope got the translation to English language Excel correct)
Let's say that the sheet index of Examplesheet is 2 (at least it is 2 the way VBA sees it).
Is there a way I could reference sheet number 2 in the VLOOKUP formula, instead of its name? My sheet names vary, but the formula should look in the same area, regardless of name.
Code: =VLOOKUP((LEFT(C4,6)),'Data from 7500'!$B$16:$G$195,6,TRUE)
And it works great, except that the data returned is off by one row. For example, the correct value for the sample name in B107 is located in G107, but the formula returns the value in cell G106. I've tried changing the TRUE to FALSE and that returns #N/A.
I am trying to obtain price of an item using vlookup() function on the identifier “0003128” (7 digits) stored with “custom” format “0000000” in one worksheet. However in other worksheet, the identifier is stored as “000312” (6 digits) with “text” format without the last digit ‘8’. I tried using the left(A,6) function on “0003128” but instead of returning “000312” it returns “3128” and I’m unable to use the vlookup() function.
View 6 Replies View RelatedI have a workbook which has data from 2 different time points (6 and 12 months) and this is signified by a prefix to an ID number which is a letter (A or B) and a number:
I need to analyse data separately so I'd like to create 2 separate workbooks, once which imports all of the 6 month data (1 row per case) and one for the 12 month data.
I thought that this would be based around a VLOOKUP of the first 1 or 2 characters but I can't work out how to integrate this with an 'IF' and link it to another workbook.
In the attached sheet I am trying to use the formula below but am getting a #NA error. I have narrowed the problem down to the use of the SEARCH and LEFT functions that I am using to determine the lookup value of the VLOOKUP formula.
what I am doing wrong? If I substitute the SEARCH and LEFT function with the number "14" it works just fine. You can find examples of both in cells B29 and C29 on the rename tab.
XL 2003
I want to combine these three functions
=VLOOKUP(e4,AGTNAME!$A$1:$E$3086,5,0)
=LEFT(E4,FIND("/",E4)-1)
and iserror
I am trying to use the left function to include only the first account number, for example if a cell has "120122 280000" (there may be many spaces between the two numbers), I only want 120122.
So far my programming only returns the 280000 into the cell.
I am working with cell values in column "K".
I have a sheet that a VLOOKUP is looking at, and what I need is, if that VLOOKUP finds what it is looking for I need it to look at the cell 2 cells to the left, how is that done?
I am thinking something like:
Formula:
[Code] ......
I just don't know the offset part.
I need to move characters from the end of a string to the beginning.
I have a list of Exact stings that are allowed. I need to move this allowed string to the beginning.
JAN
JANS
JANTX
JANTXV
If "TR" or "T/R" are at the end of the allowed Exact string, I need that removed completely.
i.e.
JANTR = JAN
JANT/R = JAN
JANTXT/R = JANTX
PN
Desired result
1N3595UR-1JANTX = JANTX1N3595UR-1
1N4992JAN = JAN1N4992
2N1711JANS = JANS2N1711
1N4148UR-1JANTXVTR = JANTXV1N4148UR-1
I need to return the text from a string, in a similar way to they way LEFT/ RIGHT would do.
I have some text in a cell, say A1 that looks like this ->
TEXT BEGIN --- TEXT NOW ENDS
I want to return everything before / after the --- symbols.
I have the below line in a macro but the left function is not working, will not sum everything.
Range("g" & x).Value = "=SUMPRODUCT(--(Data!a2:a65536 = """ & val1 & """)*(data!t2:t65536=left(g1,2)))"