Array formula to find/return a related value

R

rlerner

New Member
Joined
Aug 28, 2009
Messages
6
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!
excel-help.jpg
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
Sort by date Sort by votes
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
 
Upvote 0
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).

HTML: 
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

HTML: 
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!
 
Upvote 0
rlerner,

Is this what your data looks like?


Excel Workbook
A
1AB1HC-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
331truncated . . . . 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?
 
Upvote 0
rlerner,

Do not wrap your screenshots in HTML codes.

Just paste the Excel Jeanie into the message area.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Solved
Array returning #N/A
2
Replies
16
Views
398
Alex Blakenburg
A
S
  • Solved
Dynamic Change the Xlookup Return Array
Replies
3
Views
516
Fluff
Fluff
M
  • Question
Trouble with a formula returning duplicate values
2
Replies
17
Views
232
Cubist
C
R
  • Question
Extract rows based upon unique data in first column of an array
Replies
2
Views
194
RJC40
R
A
  • Question
Parsing Data from cell returning multiple values
Replies
4
Views
114
Fluff
Fluff

Forum statistics

Threads
1,214,528
Messages
6,120,065
Members
448,941
Latest member
AlphaRino

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top