Do I need to use Offset Match? Or Index Match?

[syxpak]

Greetings!


I have a workbook where the user enters a zip code into cell C4. I need a formula in cell D4 to look up that zip code and return the cell in the next column. My problem is the zip codes are in columns AX, AZ, and BB.


I tried to start with the following but it returns a #VALUE! error.


=INDEX(C4,MATCH(C4,AX:AX&AZ:AZ&BB:BB,0),MATCH(C4,AX:AX&AZ:AZ&BB:BB,0))


Any help is appreciated!

 

[Hui]

Syxpak


Firstly, Welcome to the Chandoo.org Forums


When you say the zip code is in 3 columns, do you mean spread across 3 columns or in one of 3 columns ?


Can you post a sample file for us to assist you

Refer: http://chandoo.org/forums/topic/posting-a-sample-workbook

 

[syxpak]

Hui,


Thanks for looking into my problem. I will see if I may post a sample file. In the meantime...The zip code is in one of three columns. It may reside in column AX or column AZ or column BB.


Again thanks for the help!

 

[syxpak]

Hui,


I found a solution that seems to work. If anyone has something better please let me know.


I created dynamic ranges for the columns containing zip codes. (ZipCode1, ZipCode2, etc.) Then used an IF statement to find the column it is in with an array entered formula. Here is a very shortened version I was using in a sample workbook before I put it into my actual workbook.


=IF($A$1=ZipCode1,$E$1,IF($A$1=ZipCode2,$G$1,$H$1))


Thanks for the help!

 

[shrivallabha]

Instead of using full column you should limit it to the maximum possible row. Lets say 2000.


1. Select Range AX1:BC2000 and name it as SrcRng

2. I am assuming that you are using Zipcodes so they will be unique and not duplicated.

3. Implement following formula in D2 [Normally entered formula]

=INDEX(AX:BC,SUMPRODUCT((SrcRng=C2)*ROW(SrcRng)),SUMPRODUCT((SrcRng=C2)*COLUMN(SrcRng))-48)

Notice 48 which is column AX1 [50] - 1 and the adjustment of the offset i.e. by 1.

 

[Faseeh]

Hi syxpak,


You can try this as well:


Your data looks like this:

[pre]

Code	Value	Code	Value	Code	Value
11111111	1	11111122	9	11111156	18
11111112	2	11111123	12	11111157	19
11111113	3	11111124	13	11111158	20
11111114	4	11111125	14	11111159	21
11111115	5	11111126	15	11111160	22
11111116	6	11111127	16	11111161	23
11111117	7	11111128	17	11111162	24[/pre]

=INDIRECT(ADDRESS(SUM(IF(AX2:BC8=C4,ROW(AX2:BC8))),SUM(IF(AX2:BC8=C4,COLUMN(AX2:BC8)))+1))

Assumed your data between AX2:BC8.

Regards,

 

[syxpak]

Thanks for the help everyone.


Awesome formulas! I like these better than mine.