Formula help

[Guest]

Hi,
I posted this question yesterday thinking I could use SUMPRODUCT to get the
values I need, but looking at the responses I got I'm pretty sure I need to
find another way. I have 2 spreadsheets that I need to compare with each
other. Spreadsheet #1 has:
BegBatch EndBatch Tape#
38402 38508 82

Spreadsheet #2 has a Batch# in Col. H, such as 38415. I need to see if the
38415 is in the range 38402 - 38508 in Spreadsheet #1 and return the Tape# to
Spreadsheet #2. The Beg and End Batch range the Batch# is in could be in any
row in the spreadsheet, so the formula needs to look at a range, not just 1
row. Any suggestions would be very appreciated. TIA

 

[Peo Sjoblom]

As long as there are only one TRUE condition it will work if the values in
the tape column are numbers, if text in tape and the values in begbatch and
endwatch are numbers

=INDEX(Sheet1!C2:C100,MATCH(1,(Sheet1!A2:A100<=H2)*(Sheet1!B2:B100>=H2),0))

entered with ctrl + shift & enter

 

[Guest]

Thank you so much for the help. I figured I had to use INDEX and MATCH but
was a little off on the formula.