finding rows that contain a data string across mutiple columns

[always_confused]

Hi,

Wonder if you can help? I have a spreadsheet containing one physical address and multiple email addresses on each row. There are 7000 rows each with up to 260 email addresses and physical address. On a seperate tab I have 140 email addresses and I want to know to which columns on the first data tab they refer.

I know that If I had one email address on the first sheet that I could use a standard VLOOKUP function, but I can't find a way to search across the 260 columns to see if any of them contain the email address in the second tab.

Any suggestions?

[Haseeb Avarakkan]

Hi,

I am not sure I fully understood.

Consider in Sheet1 A2 to down you have email addresses to find the column numbers.
Sheet2!A2:IZ1000 contains the data both addresses & email addresses.

If so try this in Sheet1!B2, with CTRL+SHIFT+ENTER,

=MATCH(A2,INDEX(Sheet2!$A$2:$IZ$1000,SMALL(IF(Sheet2!$A$2:$IZ$1000=A2,ROW(Sheet2!$A$2:$A$1000)-ROW(Sheet2!$A$2)+1),1),0),0)

Then copy down.

[always_confused]

Hi Haseeb,

Apologies that I did not make myself clear! and Thank you for your suggestion.

To try and clarify what I am seeking to achieve, I have created a sample spreadsheet that represents the (large) sheet I am working on. See attached.

What I want to do is to lookup across the range of email addresses in sheet 1 and when the email address is found, it returns the column reference in the A column into Sheet 2 column B.

Once I have the Column Reference associated with an email address I'm sorted.

Does that many any more sense?

Thanks,
Simon

[Haseeb Avarakkan]

Try this with CTRL+SHIFT+ENTER, rather than just ENTER in B2, then copy down.

=IFERROR(ADDRESS(SMALL(IF(Sheet1!$B$2:$F$20=$A2,ROW(Sheet1!B$2:B$20)),1),SMALL(IF(Sheet1!$B$2:$F$20=$A2,COLUMN(Sheet1!B$2:F$2)),1),4),"N/A")

[always_confused]

Thanks again Haseeb.

When I implement this formula into my data, It returns me results but the row number is either 4 or 8 rows adrift from where it should be. Is there anything in the formula that could be causing this, or have I messed up?

Thanks,
Simon