Vlookup returns Array

**blueiris8** · 03-22-2013, 10:57 AM

I've seen a lot of Vlookup and Array questions, but none that work for what I'm doing. I have two worksheets, in worksheet A, Column A has a list of 100 values (many duplicates). In Worksheet B, in Column C is a list of values that match Worksheet A, Column A (no duplicates). In Column E is a list of values.

What I need a single formula that returns an array Column E values, where Worksheet A: Column A = Worksheet B: Column C. So the end result will be an array of 100 Column E values (many duplicates).

Example:

Column A	Column C	Column E
Yellow	Red	3
Red	Yellow	5
Yellow	Blue	4
Yellow
Blue
Red

Formula Result: (5;3;5;5;4;3)

**benishiryo** · 03-22-2013, 11:23 AM

hi blueiris8. might be missing something here, but that's exactly what the VLOOKUP does. so assuming your data starts from A2, try this in Worksheet A, B2:
=VLOOKUP(A2,Sheet2!$C$2:$E$100,3,0)

if i'm wrong, do upload a sample Excel file. Make sure there is just enough data to make it clear what is needed. Include BEFORE/AFTER sheets if needed to show the process you're trying to complete or automate. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

**blueiris8** · 03-22-2013, 11:28 AM

Originally Posted by benishiryo

hi blueiris8. might be missing something here, but that's exactly what the VLOOKUP does. so assuming your data starts from A2, try this in Worksheet A, B2:
=VLOOKUP(A2,Sheet2!$C$2:$E$100,3,0)

if i'm wrong, do upload a sample Excel file. Make sure there is just enough data to make it clear what is needed. Include BEFORE/AFTER sheets if needed to show the process you're trying to complete or automate. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

Vlookup only returns one value at a time. Instead of looking up A2, I would need A2:A100 in the vlookup (which doesn't work in vlookup). I need a whole array of values. I've seen some Index/Match formulas that were close, but I'm pretty sure Vlookup doesn't work.

**Jonmo1** · 03-22-2013, 11:42 AM

You're right, vlookup does not return an array.

What's the ultimate goal?
What do you want to actually DO with the resulting array once you have it?
Do you want to SUM the results?

In your given examle, is the ultimate result 25 ?

**blueiris8** · 03-22-2013, 11:49 AM

Originally Posted by Jonmo1

You're right, vlookup does not return an array.

What's the ultimate goal?
What do you want to actually DO with the resulting array once you have it?
Do you want to SUM the results?

In your given examle, is the ultimate result 25 ?

I need to concatenate the array with a couple more arrays. I used numbers in the example just be illustrative, I'm actually going after text.

**benishiryo** · 03-22-2013, 08:21 PM

ok, maybe something like this:
=SUMIF(Sheet2!C2:C100,A2:A100,Sheet2!E2:E100)

press F2 on the cell where the formula is & press F9 to calculate. you should see something like this:
={5;3;5;5;4;3;0;0;0;.... & so on

that's probably the best Excel functions can do, unless you want to concatenate cell by cells. otherwise, consider VBA help

**bebo021999** · 03-22-2013, 10:25 PM

This kind of Lookup should be done with HLOOKUP in combination with MATCH:
=HLOOKUP("Number",Sheet2!$C$1:$E$4,MATCH($A$2:$A$7,Sheet2!$C$1:$C$4,0),0)

PHP Code:


Sheet1!ColA---Sheet2!ColC-----Sheet2!ColE

1-Color---------- Color---------Number



2-Yellow -------- Red---------    3

3-Red-------------Yellow-----    5

4-Yellow----------Blue-------    4

5-Yellow

6-Blue

7-Red