Vlookup thing is that we can use this formula if we have the data in right if we want to lookup value of left side then what should we do?

It is a function in excel that will look up to a value or data located in different sheet or in different cells. It is very helpful function to look for a bunch of data and compair and extract the exact information. syntax: =vlookup(lookup_value,table_array, col_index_num,[range_lookup])

you can a tutorial here.

please rate.

the issue is that you may be trying to find the the data which is not there in the table PROV hence you need to have a look at the data in the table PROV
first see if the data you are trying to find is there in the table or not (use crtl+f) to see if the data is there in the table.

vlookup can only locate values within the lookup range and to the right of the lookup column. to address your concern, i'd advise you to create a dummy column within the lookup range (and hide it) and link it to the lookup column. this way, we won't need a (-) column index number.

I'm assuming you'd like to assign a numerical value to cardinal references (1st, 2nd, 3rd, 4th, etc.).

Best way to do this is to create a quick lookup table on a separate sheet of the same .xls document. Down at the bottom of the page, click on Sheet2 and create a quick table where column A has 1st, 2nd, 3rd, etc. and column B has your values.

Now click back to Sheet1 where your data is and in A2 put this formula:
=VLookup(A1, Sheet2!$A$1:$B$x, 2, FALSE)
Note: replace the lower case x in the formula above with the number of the last row of data in your lookup table on Sheet2.

You can copy and paste this formula down the column to calculate your other values.

Hope that helps!
Terry

Hi vrusha,

Your right hlookup is very simular to vlookup, the key difference is it searches along the top row of the table, finds the matching data and gives you one of the below cells (depending on how you write the formula), just think of a vlookup on it's side.

The formula works like this:
=HLOOKUP(lookup value, table, row_index_number, range_lookup)

lookup value = is the value you want to match against the table i.e. ABBA

table = the range of cells that make up the table you want to search i.e. A1:D300

row_index_number = the number of rows from the top of the table you want to get the value from, 1 is the top of the table, 2 is directly below

range_lookup = if you want an exact match type FALSE, if you want the nearest match type TRUE

Hi Hss Holdings,

The easiest way to do this is to use a formula called vlookup. You need to have the raw data some where in the workbook, but it can be a separate sheet. Put all the raw data in a table, make sure the account number is on the furthest left column, then start entering the formulas

vlookup(value_to_find, table_to_check, column_index_number, range_lookup(true/false))

value_to_find = the value you want to find on the left most column of the table
table_to_check = the table you want to find the data from
column_index_number = the column number of the data field you want to return into this cell, 1 = the left most column.
range_lookup = false for an exact match, true for the nearest match

Say the table is on sheet2 between A1 and E300

B15 is whatever you type, so no formula needed here
B12 =VLOOKUP(C1,Sheet2!A1:E300,2,FALSE)
G12 = B12 =VLOOKUP(C1,Sheet2!A1:E300,3,FALSE)
etc

If you can move your name column (C) to the first column, you could leverage the VLOOKUP formula pretty easily.
To do this, do the following:
1) Move the C Column to be the A Column, shifting all other columns to the right.
2) (optional) Insert a new row at the top of the sheet (to hold the formula & seach value)
3) Use A1 as your search field.
4) In A2, enter the following formula:
=VLOOKUP($A$1,$A$2:$C$6,3,)

Describing above parameters, in the formula:
$A$1 -> the search field (name your looking for).
$A$2:$C$6 -> The table/grid you wish to search and return values from. The left most column (A) must contain the values to be searched.
3 -> is the column number (A=1,B=2,C=3, etc) within the table/grid to return.

If you cannot make the name column your first (A) column, there are more complex ways to do this. For instance, create a new sheet which redisplays the info in the structure easier for this method, and perform the VLOOKUP on that data. Other options might exist in creating a complex formula that would get you what you want.
Also, if you can sort column A (names) it would find results faster, if your data set is large.

I love vlookup! Suppose you have 1 worksheet with song numbers and titles in Row 1, Cols A:B: Song# Title 123 Love Me Tender 234 Blue Suede Shoes 345 Dixie Another worksheet has song number and performer in Row 1, Cols A:B Song# Performer 123 Elvis Presley 234 Carl Perkins 456 Cher Notice there is NO performer for song number 345 in the 2nd worksheet. Now in the 1st work sheet, cell C2 insert this LOOKUP function: =LOOKUP(A2,Sheet2!A:B) Copy that cell to row 3 and row 4 in Col C. You should get a Performer for all songs even though there is not a song number 345 in the performer worksheet. Help me out Mr. VLOOKUP. Insert this VLOOKUP function in cell C2 of the first worksheet: =VLOOKUP(A2,Sheet2!A:B,2,0) Copy that cell to row 3 and row 4 Col C. You should get the performer names for the 1st 2 songs, but not for 345 Dixie. The result should be #N/A. That means VLOOKUP could not find a DIRECT match for song 345 in the second worksheet. That is why I prefer VLOOKUP over LOOKUP. I have found this explaination of the VLOOKUP parameters helpful: 1. Needle (A2) 2. Haystack (Sheet2!A:B) 3. RELATIVE Col containing result (2) 4. Need DIRECT MATCH ONLY (0) Hope this helps. Let me know if you have any questions.