ANmarS - Search results

Excel/VBA expert since Sep 1997 (21 years 2 months)

We are moving ...

We are moving to another location, better, faster and more resources .... check it out ANmar.Systems

13 Q & A posts tagged with VLOOKUP

Sat
Nov 19
2011

What function to use in excel?

I have a range of the first five digits of telephone numbers and I need to replace them into country name. so i need to compare very single digit of these telephone numbers with the range of prefixes i have before deciding the country name. is there any function to do that ?

I used lookup function but it compare the number exactly
example if i have the number 27800 and in my prefixes i only have 27 it will not recognize it.


Answer

You can also still using VLOOKUP but with some modifications

Modify your list of prefixes to have the entire number with zeros
so 27 becomes 27000, 15 to become 15000, etc

here you can use the VLOOKUP with Range lookup to be TRUE, like this

=VLOOKUP( A1, D:L, 2, TRUE)

The "TRUE" will let you search for the nearest item to the searched one before it

Assuming your list of prefixes is sorted in ascendant order by prefix number

Excel help has more details about the "TRUE" in VLOOKUP

Saturday 11/19/2011 11:42:40 AM
Sat
Nov 19
2011

What function to use in excel?

I have a range of the first five digits of telephone numbers and I need to categorize them into country name. so i need to compare very single digit of the telephone numbers before deciding the country name. is there any function to do that ?


Answer

Use VLOOKUP with TRUE in "range lookup" as in this question
http://answers.yahoo.com/question/index?…

You can also use other suggestions there

Saturday 11/19/2011 9:29:16 AM
Wed
May 12
2010

In Excel 2007, how do I make specific number values correspond to specific text values?

I'm trying to create a template for entering social science questionnaires, and SPSS has spoiled me. When I start entering the data, I want to able to type numbers and then have the correct text fill the box. For example, one question concerns race. I want to be able to type "1" and have "caucasian" automatically appear in the box. However, I want to RETAIN the number values so that I can easily run statistical analyses. Can Excel do this? Someone please help!


Answer

Yes, it can
This called VLOOKUP function, it is very popular and widely used

Here is a lesson on how to do that
http://www.vbaxlman.com/Lessons/?ID=b1vi…

VBAXLMan

Wednesday 5/12/2010 8:55:57 PM
Tue
May 4
2010

How to import into MS Word ,data from two worksheets from Workbook of MS Excel file ?

I have MS-excel Data with a customer sheet with address details on one sheet & Items bought by a customer on other sheet,by using mail merge I am able to generate a letter for each customer by using customer sheet,I want to include details of items bought(from that items sheet).


Answer

I suggest you having a third worksheet that list the customers and their buying details
Then mail merge it

You might need to use formulas to do so, like VLOOKUP, MATCH, INDEX, IF, COUNTA, etc
use the customerID to grap these info

VBAXLMan

Tuesday 5/4/2010 3:40:57 AM
Sun
May 2
2010

Can someone help me with a lookup table in Excel 2007?

I have to "create a lookup table that will help determine the letter grade for each student. The percent levels should be entered in the first column; the corresponding letter grades entered in the second column. (Assume a typical grading scale where 90%=A, 80%=B, etc.) Name the lookup table Grade_table. Arrange the % levels and corresponding grades from lowest to highest. Please help!!!


Answer

You need to use the VLOOKUP formula
In addition to Name feature

First you need to do the table, somewhere in any free area (say G1 to H10) put the percentage in G, corresponding Grade letter in H
Then, select that range, and go to Formula > Name, type in the name you have "Grade_table"

Then in cell A1, put any grade, say 76
In B1, paste this
=VLOOKUP(A1, Grade_table,2,TRUE)

You need to sort the Grade_table ascending by column G

VBAXLMan

Sunday 5/2/2010 7:40:01 PM
Fri
Apr 9
2010

How do I populate corresponding cells with a code in excel?

Hello Everyone,
I have been struggling this for so long , your help will be much appreciated.
I am trying to make an invoice for a sales order with many different items. I need to know how I can populate the corresponding cells automatically from data stored somewhere else when I enter the product code for the specific item so I don't have to always enter it manually. For example, if I enter product code A-1 on cell A, I want the other cells on the line to be filled out automatically like "Hair Styling Iron" on cell B and "$50" on cell C and so on...

Thanks!


Answer

Once you have this table in some sheet (say Sheet1)
A, B, C, D
ID, Name, Description, Price
ID1, Name1, Hair Styling Iron, 50

Then in your invoice sheet, let me assume you have cell B15 has the Item ID that you want to populate its values, then in cell C15, paste this
=VLOOKUP( $B15, Sheet1!$A:$D, 2, FALSE)
And paste it down to fill other items

Then in D15, paste this
=VLOOKUP( $B15, Sheet1!$A:$D, 3, FALSE)
And paste it down to fill other items

=VLOOKUP( $B15, Sheet1!$A:$D, 4, FALSE)
And paste it down to fill other items

And you got what you are looking for

Let me know if that make sense or not

VBAXLMan

Friday 4/9/2010 9:02:55 AM
Wed
Apr 22
2009

How do I write an excel formula to display an asnswer if a Number in one cell appears in another column?

I need to find out a formula that will display an answer in a blank Cell if the number contained in another cell in the same row is present within a whole column on another worksheet.

For example, if 1 is present in cell B1 in worksheet 1, then I want the formula to be able to search the whole of column C in worksheet 2 for that number (1) and if it is present display a custom text (e.g. training) in the blank cell A1 on worksheet 1.

I know my description is pretty garbled, but if someone could help I’d be eternally grateful.


Answer

I guess you want to know the famous VLOOKUP function

Here is a free video lesson that talks about that
http://www.file1.net/lesson.asp?id=b1vi8…


Check out other lessons below

VBAXLMan

Wednesday 4/22/2009 2:26:46 AM
Sat
Apr 18
2009

VLOOKUP & HLOOKUP IN MS-EXCEL?

plz. tell me in brief abour "VLOOKUP & HLOOKUP" with examples.


Answer

Here is a free video lesson about VLOOKUP
http://file1.net/lesson.asp?id=b1vi8438X…

If this is basic for you try these
http://file1.net/search.asp?query=vlooku…

VBAXLMan

Saturday 4/18/2009 12:04:40 AM
Thu
Apr 9
2009

I want to know the Ms-excel formulas. to use excel.?

I want to know different formulas in excel like vlookup,hlookup,pivot table,conditioning formats & links ect.,


Answer

There are a lot of websites that can help you
try these

http://MrExcel.com
http://www.File1.net

VBAXLMan

Thursday 4/9/2009 12:16:34 AM
Wed
Apr 8
2009

Please help with an excel formula?

I am trying to make a cell look at a specific letter combination, please see my unsuccessful formula below:

=IF(I7=HO,12,IF(I7=RO,12,IF(I7=ID=12,I…

I would like it to look for them specific letters & return the number that corresponds with it.


Answer

I would rather you to do the proper way
Which is:
1- Put the possible values expected in cell I7 in range A1 to A5 for example
2- Put the corresponding value to the cell next to it in B1 to B5
3- Now paste this in cell J7 (Next to I7)
=VLOOKUP(I7, $A$1:$B$5,2,FALSE)

Enjoy it

VBAXLMan

Wednesday 4/8/2009 7:50:56 AM
Thu
Feb 12
2009

How to create a function in Excel...a second question?

Thank you to the person that helped me yesterday...a huge help! The isanswer and match functions worked great!

I now need to write a formula to do the following: I need to find if the value in cell C1/Sheet 1 is in column D/Sheet 2. If true, return the value (text) in Column E/Sheet 2 that is in the same row as the value found in Column D. If false, return "null" or "false".

I tried the same isanswer and match formula as it seemed it was close to the solution but it didn't work. Can you help me again? :)


Answer

Paste this in cell E1 Sheet2
=IF( ISNA(MATCH( Sheet1!C1, Sheet2!D:D,0)), "","Text")

And fill it down

----- Edit ----
Yes, in this case, use this
=IF( ISNA(VLOOKUP( Sheet1C1, Sheet2!D:E,2,FALSE )),"Not found",VLOOKUP( Sheet1C1, Sheet2!D:E,2,FALSE))

This whould work

VBAXLMan

Thursday 2/12/2009 3:18:14 PM
Wed
Jan 21
2009

Searching across sheets in Excel?

How can I do a search for several items across sheets in Excel, whether using vlookup or any other function


Answer

This is the lesson for that
http://www.file1.net/lesson.asp?id=28

Enjoy it

VBAXLMan is here to feed your Excel needs

Wednesday 1/21/2009 6:54:45 PM
Sat
Jan 17
2009

I want to fix a pic in MS Excel 2007 cell and to use in an another sheet with Vlook up formula?

For each cell of employee I want to fix a pic and to use in forms with Vlookup formula


Answer

Excel functions are working with text and numbers
You can not do this with functions, instead you will need macros to do that

I would suggest to do a folder with employee pictures named with employee ids
Then insert a picturebox in that Excel file and do some macros to make it read the selected employee picture.

Something like this:
Sub ReadPic()
EmployeeID =Range("A1").value
PictureBox1.picture=LoadPicture( "D:\Pix\" & EmployeeID & ".jpg")
End Sub

This will read the picture in the picturebox assuming cell A1 had the employee id

I will be glad to help if you just send me the file you are talking about and more details to build the macro for you

VBAXLMan

Saturday 1/17/2009 10:34:54 AM

Tags

History