i have a spreadsheet with 2 sheets. the first sheet is used to print a receipt, the second has the products. I would like to type in the code on the 1st sheet and it to fill in description and price taking the data from the 2nd sheet. there are 2 columns on the 2nd sheet, code, description and price.

can anyone help with a formula or process to do this?

Sounds like VLOOKUP should work for you. Look it up in the Excel Help files and see if you can get it to work.

If not, come on back with some more details and we'll see what we can do to help.

thanks, i tried VLOOKUP but i cant quite figure out what conditions to put in.

You'll note that in my response I said: "If not, come on back with some more details and we'll see what we can do to help."

Keep in mind that we can't see your spreadsheet from where we're sitting, so if

youcan't figure out conditions to put in,wecertainly can't.You'll need to give us some idea how how the worksheets are laid out. Some examples of the data in Sheet 2, what columns it's in etc. might help

What(where?)do you plan to enter data in Sheet 1 and what(where?)do you expect as results?

ok sorry about that. sheet 1 starting at A7-A9 for code, des, price.

sheet 2 for lookup is A1-A786 for code, B1-B786 for des and C1-C786 for price.

i want to type the code into A7 and it autofill A8 with des and A9 with price. hope this helps. if not let me know what else you need to know and ill provide it.

thanks.

As per the Help files, the syntax of VLOOKUP is: VLOOKUP(

lookup_value, table_array, col_index_num, range_lookup)Your

lookup_valueis A7

Yourtable_arrayis Sheet2!$A$!:$C$786

Yourcol_index_numis 2 for Column B and 3 for Column C

Yourrange_lookupis FALSE or 0, since you are looking for an exact match

Therefore A8 should contain:=VLOOKUP(A7, Sheet2!$A$1:$C$786, 2, 0)

This will "look up" the value you put in A7 in the list of codes in the first column of the

table_array(Sheet2!A1:C786) and return the value from the 2nd column of that table, which would be Sheet2!B1:B786.To pull values from Column C, use =VLOOKUP(A7, Sheet2!$A$1:$C$786, 3, 0)

Note: The

col_index_numis 2 for Column B and 3 for Column C only because they are the 2nd and 3rd columns of yourtable_array, not because they are the 2nd and 3rd column of the spreadsheet.For example, if your

table_arraywas G5:J47, acol_index_numof 2 would refer to Column H, the 2nd column of thetable_array. Column J would have acol_index_numof 4.

thanks for your help. that worked perfectly, i see where i was going wrong now. is there a way to make the cell blank rather than displaying an #N/A if there is no value for it lookup?

dont worry, i used put the vlookup in an IF(ISBLANK) to void the blank value.

Another (more common) option is to use ISNA(). ISNA handles

allsituations where VLOOKUP returns #N/A, not just when thelookup_valuecell is blank.

thanks for that, i will experiment with that and see how it works out. thanks alot for all your help with this.

Ask Your Question

Weekly Poll

Do you think end-to-end encryption should be banned?

Discuss in The Lounge

Poll History