CODEWITHSUNDEEP
rangematchexcel-formula
Tom Chambers
Tom Chambers

Reputation: 374

Excel: Check if Cell value exists in Column, and return a value in the same row as match but different column

In a single workbook i have 2 worksheets named Master and Software.

I want to search a range of cells in column F of Software and IF the value '1' exists in it AND IF 'Software' column B has a text value of 'Antivirus', I want to return to 'Master' the cell value of 'Software' column F FOR THE ROW THAT MATCHES THE VALUE 1.

I tried this but i get errors that i can't debug:

=IFERROR(INDEX($Software!F$7:$Software!F$1000,MATCH(1,$Software!A$7:$Software!A$1000,0),1),"no match")

Anyone see what i'm doing wrong please?

It's like a steering wheel down my pants - driving me nuts.

Thanks

Upvotes: 1

Views: 4734

Answers (1)

Alan Waage
Alan Waage

Reputation: 624

Does the value '1' you are seeking change based on data in Master?

Can you move columns around in the Software tab?

vlookup might provide an easier solution.

As a sample mockup for the 'Software' tab:

Row Software                Number
1   test                    1
2   test                    3
3   AntiVirus               1
4   AntiVirus               2
5   test                    4

Then a forumla on Master to pull back Row 3 that would match your question would be:

=INDEX(Software!A:A,MATCH(1,(Software!B:B = "AntiVirus")*(Software!F:F=1),0))

***Important, when you put this in the cell, to get Excel to evaluate it as an array you must press CTRL + Shift + Enter This will add { } around the function when viewed in the forumla bar.

Upvotes: 0

Related Questions