• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

array formula

G

greg.begin

Member
I have a list of ID numbers, in these ID number are 6 columns of possible part numbers. I have another tab that has the part number it is looking for and wants to see if that number is one of the 6 possible numbers and if so, what is the ID number. i tried an array formula, but it only works when i choose 1 column and doesnot work when i select an array.


In sheet 1, the Id numbers are in column A and the part numbers are B through G,

the formula is on sheet 2 column G and referencing column A for the part number to look for in sheet 1.
 
Hi Greg ,


I am not clear on your post ; please clarify.


1. You have a worksheet where column A consists of a list of ID numbers.


2. In this sheet , columns B through G are having part numbers.


3. On another sheet , you have a formula in column G which references a part number in column A.


4. You wish to check whether this part number is present anywhere in columns B through G on the first sheet ; if yes , then you want the corresponding ID number ; if no , what is to be done ?


5. The part number may be present in any column , from B through G.


Is all of the above correct ?


Narayan
 
Hi, greg.begin!


Assuming that there are repeated part numbers for different ID numbers, there's a solution without array formulas but with helper columns. Just check if it works for you.


I used your 6 defined part numbers per ID; if you happen to change this value, adjust the 6 in the formulas of columns J and L of first sheet to the new value.


I used 5 occurrences for the IDs for a given part number; if more needed:

a) copy column R to the right in first sheet

b) copy column K to the right in second sheet

c) adjust range $M:$R in formulas of column G thru end of second sheet


Link:

http://dl.dropbox.com/u/60558749/array%20formula%20%28for%20greg.begin%20at%20chandoo.org%29.xlsx


Regards!


PS: BTW, which was the array formula you've been working on?
 
Sorry I tried every option to share my file and due to the nature of my work, all sites are blocked or not working. I will try to type it here


'ID# Part Number1 Part number2 part number3 Part number4 Partnumber5 Etc...

' 1 4732984709 49857hhH58 13987329 165489f 45481684

' 2 9998437272 756362teLh 66382981 354651r 98456442

' 3 7643728632 467376hiHh 88743729 879845i 98465431


'next sheet

'Part number to look for Corresponding ID#

'49857hhH58 Need formula here!

'88743729 Need formula here!

'98465431 Need formula here!
 
Sorry I tried every option to share my file and due to the nature of my work, all sites are blocked or not working. I will try to type it here


'ID# 'Part 'Number1 'Part number2 'part number3 'Part number4 'Part number5 Etc...

' 1 '4732984709 '49857hhH58 '13987329 '165489f '45481684

' 2 '9998437272 '756362teLh '66382981 '354651r '98456442

' 3 '7643728632 '467376hiHh '88743729 '879845i '98465431


'next sheet

'Part number to look for 'Corresponding ID#

'49857hhH58 'Need formula here!

'88743729 'Need formula here!

'98465431 'Need formula here!


trying it again to fix formatting
 
Hi, greg.begin!

Could you get access to my uploaded file? If you can't, maybe I can email it to you?

Regards!
 
Hi, greg.begin!

The link is in one of my previous posts.

Post:

http://chandoo.org/forums/topic/array-formula-1#post-24788

Link:

http://dl.dropbox.com/u/60558749/array%20formula%20%28for%20greg.begin%20at%20chandoo.org%29.xlsx

Regards!
 
Hi Greg ,


Try the following formula :

[pre]
Code: 
=INDEX(Sheet3!$A$10:$A$12,MIN(IF(Sheet3!$B$10:$F$12=Sheet1!A10,ROW(Sheet3!$B$10:$F$12)))-ROW(Sheet3!$A$10)+1)
[/pre]
I have assumed the following :


1. Your main data , the IDs and the part numbers , is in Sheet3 , in the range A10:F12 , where column A has the IDs , and the other columns have the part numbers.


2. Your part number list , for which you need the ID match , is in Sheet1 , from A10 downwards.


3. The formula given above needs to be entered in cell B10 downwards , as an array formula ( using CTRL SHIFT ENTER ).


4. In case there is no match , the above formula will return a #VALUE! error.


Courtesy : http://www.excelforum.com/excel-general/773770-match-across-multiple-columns.html


Narayan
 
You must log in or register to reply here.
Back
Top