How to perform lookup when lookup value not in first column of array?

[neo95gt]

I have 2 worksheets. The first is a single column of values. The second is a data array with multiple rows and columns.

I need to perform a lookup/search of the values in worksheet 1 against the data array in worksheet 2. The returned value needs to be the cell adjacent to the right of the matched cell. I cannot simply use vlookup since the lookup values are not necessarily in the first column.

I've been messing around with the index, match, countif functions but can't get it right. Can anyone help? I've included a simplified example below. The real data I'm working with actually has 3000+ columns of data, the data points repeat every 20 columns so there are 150 "sets" of data spreading horizontally across the spreadsheet.


Worksheet 1 (lookup values):

Color	Returned Value
Green	4
Orange	7

Worksheet 2 (data array):

Color	Number	Color	Number
Blue	1	Black	2
Red	3	Green	4
White	5	Yellow	6
Orange	7	Brown	8
Silver	9	Pink	10

So the formula I'm looking to create will be for the "Returned Value" column in the first table. In this example, Green is the lookup value, it is found in the data array (table 2) and returns the value located next to the cell (4). Same logic applies to the orange example and the response of 7.

Hope this makes sense. Any help is appreciated.

[TMS]

You have two choices. Simple first choice, make it one list of colours and values. Second messier choice, treat it as two sets of colour and value pairs. Lookup the value in the first list and use IFERROR to make it look at the second list if it's not in the first list.

Regards, TMS

[AlKey]

Try this formula in B2 and copy down

=SUMIFS($B$6:$D$10,$A$6:$C$10,A2)

if you using Excel 2003 use this array formula

=SUM(IF($A$6:$C$10=A2,$B$6:$D$10))

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

	A	B	C	D
1	Color	Returned Value
2	Green	4
3	Orange	7
4	Pink	10
5
6	Blue	1	Black	2
7	Red	3	Green	4
8	White	5	Yellow	6
9	Orange	7	Brown	8
10	Silver	9	Pink	10

[neo95gt]

You have two choices. Simple first choice, make it one list of colours and values. Second messier choice, treat it as two sets of colour and value pairs. Lookup the value in the first list and use IFERROR to make it look at the second list if it's not in the first list.

Regards, TMS

The actual data I'm working with is much larger so both of your suggestions would not work. I'm working with 3,000+ columns and 400+ rows. So the solution needs to be able to handle scale. That's why I need a formula that will search the entire worksheet and return the data point I need.

[TMS]

I think you could extend the ranges in AlKey's solution. It just needs to refer to alternating columns. Have you tried that.

It helps if you tell us what you're really trying to do instead of an over simplified example.

[neo95gt]

I think you could extend the ranges in AlKey's solution. It just needs to refer to alternating columns. Have you tried that.

It helps if you tell us what you're really trying to do instead of an over simplified example.

The actual data I'm working with contains a table array 3,000 columns long. The data points repeat every 20 columns. I also will need the return a value that is next to it as well as 4,5,6,7 columns next to it, so I need the flexibility to return any value I want basically.

[neo95gt]

This is the solution I came up with, but couldnt figure how to return a cell +1 columns to the matched value

=Index(A1,Countif(A1:XFD400,A1))

[FDibbins]

This will work for you (based on Trvor's suggestion), you just have to adjust the ranges...
=IFERROR(VLOOKUP(A2,$A$10:$B$14,2,0),VLOOKUP(A2,$C$10:$D$14,2,0))

If you have a 3rd "set" of data, just add another IFERROR()

[neo95gt]

This will work for you (based on Trvor's suggestion), you just have to adjust the ranges...
=IFERROR(VLOOKUP(A2,$A$10:$B$14,2,0),VLOOKUP(A2,$C$10:$D$14,2,0))

If you have a 3rd "set" of data, just add another IFERROR()

yea the problem is I do have a 3rd "set" of data. I actually have like 200 sets of data, so that's why I can't just build a forumla like that. The formula needs the ability to read the entire table at once, and return a result X amount of cells away. I really wish there was a vlookup that didnt hinge on the first column in the array, but instead searched the entire table array.

[SomeDude0nline]

I edited, my response and for some reason, rather than adjusting the post it posted my new version as well as the old version. not sure how to delete the old version so here is an explanation (hopefully replacing the old version).

[SomeDude0nline]

I don't know how to work with Macros but I would suggest bringing in someone that's very familiar with Macros. I think the solution is probably to set up a ton of index match formulas. Basically you set up each formula to return the next column of matching data but as you have several hundred columns this would be too time consuming as you said. However, what I do know about macros is that they make repetitive tasks very simple. There is probably a way to set up a Macro to create all of the formulas for you. Then it would just require some simple data manipulation.

Also, have you considered lining up each pair of columns into one pair of columns? You probably have thousands of rows so this may not be a great solution but if you copy all of your data to a new spreadsheet and take columns C&D and add them to the bottom of columns A&B and so on then you could simply use one index match formula. And because you copied the information to a new spreadsheet you would still have the data in the original format on the on spreadsheet.

I think I understand what you're trying to do but if not then I'm sorry for my poor suggestions. Good Luck.

[newdoverman]

This array formula (enter with Ctrl + Shift + Enter) might work for you:

Please Login or Register  to view this content.

[newdoverman]

Here is an expanded and corrected solution. I added two columns to show that it would work with more data. The K for SMALL was also changed to 1 from the COLUMNS... which was incorrect.


Array enter (Ctrl + Shift + Enter

Please Login or Register  to view this content.