VLOOKUP or MATCH or INDEX ~ Really need help with my problem!

[TTUK]

Hi all,

Have had so much help on this forum in the past, and would love for some help and support again!
I really need help to find a solution for what I need to do: I will try to explain it in the best possible way below...

SHEET 1

______Colm F_____________________Colm N
Row 6_COMPLETE ASSY PART No.___L319 14MY__________________
​Row 7_7H22-1K003-AB8MNH_________*this is where i need my formula*
​Row 8_7H22-1K003-AC8MNH_________**Should see the number 1 if it matches**
​Row 9_7H22-1K003-BB8MNH
.

SHEET 2
______Colm B_________Colm E________________________
Row 2_REF No._______COMPLETE ASSY PART No.______
Row 3_L319 14MY______7H22-1K003-AC8MNH____________
Row 4_L319 14MY______7H22-1K003-BC8MNH____________
Row 5_L319 14MY______7H22-1K003-CB8PBG_____________



WHAT I NEED THE FORMULA TO DO:
I need cell N7 (Sheet 1) to view (Sheet 2) and match the F7 & N6 (Sheet 1), to B3 & E3 (Sheet 2) and = 1 in N7 (Sheet 1)

Now I really hope this makes, sense?
I have a spreadsheet of about 700 records to go through, and I don't want to be matching them up by hand!

Thanks all!
Charlie

 

[Aladin Akyurek]

Sheet1, N7, control+shift+enter, not just enter, and copy down:

=ISNUMBER(MATCH(F7,IF(Sheet2!$B$3:$B$6=$N$6,Sheet2!$E$3:$E$5),0))+0

 

[TTUK]

Sheet1, N7, control+shift+enter, not just enter, and copy down:

=ISNUMBER(MATCH(F7,IF(Sheet2!$B$3:$B$6=$N$6,Sheet2!$E$3:$E$5),0))+0

Hi Aladin,

Thank you, thank you, thank you!
Works brilliantly! Thank you!

Flack

 

[TTUK]

Hi,

Just a quick thought, if it is there is adds a '1' BUT, if it doesn't match a '0' (zero) appears, I'd like it to be blank "".
At the moment a =count function is adding up all the zero's...

Thanks, Flack

 

[Aladin Akyurek]

Hi,

Just a quick thought, if it is there is adds a '1' BUT, if it doesn't match a '0' (zero) appears, I'd like it to be blank "".
At the moment a =count function is adding up all the zero's...

Thanks, Flack

That is indeed what the formula effects. If you want blanks instead of 0's...

N7, control+shift+enter and copy down:

=IF(ISNUMBER(MATCH(F7,IF(Sheet2!$B$3:$B$6=$N$6,Sheet2!$E$3:$E$5),0)),1,"")