Array formula to find/return a related value

[rlerner]

I am an extreme beginner when it comes to Excel programming, so I was wondering if someone could help me.

I am getting a CSV file from a web site which has columns of text values. Let's say one has A-Z. I have a worksheet that has 2 columns where if a1 is the value A, A2 is Andrew, b1 = B, b2 = Bob, c1 = C, c2 = Charlie, etc. I need a formula that will read the values that come in a column of the CSV file, compare them to my list in the worksheet, and return the description (text value) in the column next the A-Z column, then do it again for the next row and so on. They're actually product SKUs and descriptions, I've attached a jpg that might help describe what I'm trying to do.

Any help or pointers would be greatly appreciated.

Thanks!

 

[hiker95]

rlerner,

Welcome to the MrExcel board.

You are posting a picture. This means that if this was a problem where one needed to use your data, anyone trying to help you would have to enter the data manually. That makes no sense and I doubt you'd get any anwer.

Please post a screenshot of your sheet(s), what you have and what you expect to achieve, with Excel Jeanie HTML 4 (contains graphic instructions).
http://www.excel-jeanie-html.de/html/hlp_schnell_en.php


Or, with:

Please post a screenshot of your sheet(s), what you have and what you expect to achieve, with Colo's HTML Maker.
http://www.puremis.net/excel/downloads.shtml

Instructions for using "Colo's HTML Maker":
http://www.mrexcel.com/forum/showthread.php?t=89356

 

[rlerner]

Thanks. Here is a snapshot ofthe product description list. I want to draw from the SKU from the CSV file, finding the matching SKU in column A in this sheet, and return the value of the description in column B to a column in the CSV file (below).

Product Description List

  AB1HC-SKUDescription2HC-MI-CFKT-shirt with NP logo and state of Michigan plus your personalized message below.3HC-NY-CGAHat with NP logo and state of New York plus your personalized message below.4HC-AL-CRACD Holder with NP logo and state of Alabama plus your personalized message below.5HC-NJ-CJADrink Coozie Set with NP logo and state of New Jersey plus your personalized message below.6HC-MO-CTAVisor with NP logo and state of Missouri plus your personalized message below.7HC-LA-CRAT-shirt with NP logo and state of Louisiana plus your personalized message below.8HC-PA-CJANotepad with NP logo and state of Pennsylvania plus your personalized message below.9HC-NJ-CRAFrisbee with NP logo and state of New Jersey plus your personalized message below.10HC-NY-SDSNotepad with NP logo and state of New York plus your personalized message below.11HC-OH-CSALadybug Sunglass Clipwith NP logo and state of Ohio plus your personalized message below.12HC-CA-CJBACD Case with NP logo and state of California plus your personalized message below.13HC-MN-CMBT-shirt with NP logo and state of Minnesota plus your personalized message below.14HC-AL-CSBHat with NP logo and state of Alabama plus your personalized message below.15HC-WA-CBBDrink Coozie Set with NP logo and state of Washington plus your personalized message below.16HC-WI-CTBLadybug Sunglass Clip with NP logo and state of Wisconsin plus your personalized message below.17HC-SC-CJBT-shirt with NP logo and state of South Carolina plus your personalized message below.18HC-GA-CJBT-shirt with NP logo and state of Georgia plus your personalized message below.19HC-MD-CGPFrisbee with NP logo and state of Maryland plus your personalized message below.20HC-TX-CJBT-shirt with NP logo and state of Texas plus your personalized message below.21HC-IL-CMBT-shirt with NP logo and state of Illinois plus your personalized message below.22HC-CA-CXBNotepad with NP logo and state of California plus your personalized message below.23HC-NV-CSBT-shirt with NP logo and state of Nevada plus your personalized message below.24HC-CA-CHBEVisor with NP logo and state of California plus your personalized message below.25HC-AR-CMBDrink Coozie Set with NP logo and state of Arkansas plus your personalized message below.26HC-IL-CJBT-shirt with NP logo and state of Illinois plus your personalized message below.27HC-CA-CBBLadybug Sunglass Clip NP logo and state of California plus your personalized message below.28HC-FL-CGBNotepad with NP logo and state of Florida plus your personalized message below.29HC-UT-CRBT-shirt with NP logo and state of Utah plus your personalized message below.30HC-GA-CSBHat with NP logo and state of Georgia plus your personalized message below.31truncated . . . . there are 1156 rows in this worksheet32  33 Items in red are the 3 I wanted to bring back to sheet 1 in this example 

Excel tables to the web >>  Excel Jeanie HTML 4

CSV File from web site

CSVI_Export_NP Order Export_29-

  ABCDEFGHIJK1 first_name last_name address_1 address_2 city state zip product_sku product_name product_attribute create column here & return matching data from sheet 2 col 2
2RobertIannitto2940 Southfield Rd MonroeMI48781HC-MI-CFKHC T-Shirt MichiganlargeT-shirt with NP logo and state of Michigan plus your personalized message below.3Tom McConnell171 W. 12 St.Apt. 1311New YorkNY10012HC-NY-SDSHC Notepad New York Notepad with NP logo and state of New York plus your personalized message below.4SaraBonatz106 Playford Lane Silver SpringMD 20219HC-MD-CGPHC Frisbee MarylandredFrisbee with NP logo and state of Maryland plus your personalized message below.5           6This is the CSV download from the web site. Would like to search column A of Worksheet 2 (Product Description) and return actual description (column B)  

Excel tables to the web >>  Excel Jeanie HTML 4

Thanks again for your assistance!

 

[hiker95]

rlerner,

Is this what your data looks like?

Excel Workbook
	A
1	AB1HC-SKUDescription2HC-MI-CFKT-shirt with NP logo and state of Michigan plus your personalized message below.3HC-NY-CGAHat with NP logo and state of New York plus your personalized message below.4HC-AL-CRACD Holder with NP logo and state of Alabama plus your personalized message below.5HC-NJ-CJADrink Coozie Set with NP logo and state of New Jersey plus your personalized message below.6HC-MO-CTAVisor with NP logo and state of Missouri plus your personalized message below.7HC-LA-CRAT-shirt with NP logo and state of Louisiana plus your personalized message below.8HC-PA-CJANotepad with NP logo and state of Pennsylvania plus your personalized message below.9HC-NJ-CRAFrisbee with NP logo and state of New Jersey plus your personalized message below.10HC-NY-SDSNotepad with NP logo and state of New York plus your personalized message below.11HC-OH-CSALadybug Sunglass Clipwith NP logo and state of Ohio plus your personalized message below.12HC-CA-CJBACD Case with NP logo and state of California plus your personalized message below.13HC-MN-CMBT-shirt with NP logo and state of Minnesota plus your personalized message below.14HC-AL-CSBHat with NP logo and state of Alabama plus your personalized message below.15HC-WA-CBBDrink Coozie Set with NP logo and state of Washington plus your personalized message below.16HC-WI-CTBLadybug Sunglass Clip with NP logo and state of Wisconsin plus your personalized message below.17HC-SC-CJBT-shirt with NP logo and state of South Carolina plus your personalized message below.18HC-GA-CJBT-shirt with NP logo and state of Georgia plus your personalized message below.19HC-MD-CGPFrisbee with NP logo and state of Maryland plus your personalized message below.20HC-TX-CJBT-shirt with NP logo and state of Texas plus your personalized message below.21HC-IL-CMBT-shirt with NP logo and state of Illinois plus your personalized message below.22HC-CA-CXBNotepad with NP logo and state of California plus your personalized message below.23HC-NV-CSBT-shirt with NP logo and state of Nevada plus your personalized message below.24HC-CA-CHBEVisor with NP logo and state of California plus your personalized message below.25HC-AR-CMBDrink Coozie Set with NP logo and state of Arkansas plus your personalized message below.26HC-IL-CJBT-shirt with NP logo and state of Illinois plus your personalized message below.27HC-CA-CBBLadybug Sunglass Clip NP logo and state of California plus your personalized message below.28HC-FL-CGBNotepad with NP logo and state of Florida plus your personalized message below.29HC-UT-CRBT-shirt with NP logo and state of Utah plus your personalized message below.30HC-GA-CSBHat with NP logo and state of Georgia plus your personalized message below.
2
3	31truncated . . . . there are 1156 rows in this worksheet32 33 Items in red are the 3 I wanted to bring back to sheet 1 in this example
4
Sheet1

If it is, then what do you want to do with it?

 

[hiker95]

rlerner,

Do not wrap your screenshots in HTML codes.

Just paste the Excel Jeanie into the message area.

 

[rlerner]

Sorry, I guessed I screwed up the Excel Jeanie thing.

I got an answer from the folks at excelforum.com. It was the vlookup function. Worked like acharm.

Thanks for your time, I really appreciate it. Am now under control.

RL

 

[DonkeyOte]

As mentioned at EF.com try to avoid x-posting as you will quickly alienate people - ie as a general rule people prefer to not spend their free time helping if you may have already obtained a resolution elsewhere... it's not a no-no either here or there but we do ask you have the courtesy to post all associated links.

Thanks for your co-operation.

http://www.excelforum.com/excel-wor...ula-to-query-an-array-and-return-a-value.html